SQL Server error for different date format of Regional date format and Database date format

4
votes

My regional date setting is set to

yyyymmdd

Now, my SQL Server has the default DateTime setting as 

mm/dd/yyyy

My database column has a datatype of DateTime, and the values it contains are in the same format of the database.

My code is written in Delphi7.

And when it come to fetcch record from the database, it fires a query like given below:

select * 
from EMPLOYEE 
where JOINING_DATE > '08292017'

In above query, expected date is 08/29/2017 or 08-29-2017.

In Delphi 7, I'm using GetLocaleChar method to get the value of separator from the region settings; below is the code for this:

GetLocaleChar(DefaultLCID, LOCALE_SDATE, '/')

where 

DefaultLCID := GetThreadLocale;

Another question: does it matter in SQL Server that which separator we are using irrespective of separator used in database column?

1
sql-serverdatedelphidatetimedelphi-7
Use parametrized query and you should be fine. – Victoria
Beside that you should parametrize your query this can be an interesting read karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput – GuidoG

1 Answers

6
votes

My database field has datatype as - DateTime and the values it contains is in the same format of database.

Datetime values in SQL Server are in binary format. The string value mm/dd/yyyy of datetime values is controlled entirely by the application presentation layer.

And when it come to fetcch record from the database, it fires a query like given below: select * from EMPLOYEE where JOINING_DATE > '08292017'

I suggest you simply parameterize the query instead of passing a date literal string. That will avoid date format string issues as well as provide other benefits like performance and security.

If you must pass a date or datetime literal to SQL Server, use unseparated YYYYMMDD format so that it is correctly interpreted regardless of the SQL session DATEFORMAT setting.