DevTest Date Differences Between English and British-English on SQL Server

book

Article ID: 131642

calendar_today

Updated On:

Products

CA Application Test Service Virtualization CA Continuous Application Insight (PathFinder)

Issue/Introduction

Need to have DBA to set the Default language of the user accessing the database to English and not British-English..

The different between US-English and BRITISH English date is

US English: YYYY-MM-DD
British English: YYYY-DD-MM

The following query runs fine even with BRITISH English, but it tries to fetch records from 4th Nov 2019, hence it didn't return any result:
**********************************************************************************************************************************************************
SELECT PERIODIC_METRIC_ID, METRIC_NAME, METRIC_MAX_VALUE, METRIC_MIN_VALUE, METRIC_VALUE, PERIOD_LENGTH, PERIOD_TS FROM VSE_METRICS_PERIODICS
WHERE 0 = 0
AND 0 = 0
AND PERIOD_TS BETWEEN '2019-04-11 21:37:03.261' AND '2019-04-11 21:50:03.267'

The following query would throw JASPER error because 23 is not a valid month
*******************************************************************************************
SELECT PERIODIC_METRIC_ID, METRIC_NAME, METRIC_MAX_VALUE, METRIC_MIN_VALUE, METRIC_VALUE, PERIOD_LENGTH, PERIOD_TS FROM VSE_METRICS_PERIODICS
WHERE 0 = 0
AND 0 = 0
AND PERIOD_TS BETWEEN '2019-04-23 16:54:10.993' AND '2019-04-23 17:04:10.993'

Environment

Release:
Component: ITKOTF

Resolution

For UK customer's please set the Database user's default language to English instead of British-English.