Dates in SQL

[villan60]

What is the story with dates in SQL? Do they have to be in American format? if so, how will I convert my dates in my fields to that format when I construct queries in SQL in VBA code?

thanks
dax

 

[mvdmeij]

The default is US english on SQL server, but on the PC the regional settings will be used to display/input dates.

So, I guess you don't need to make conversions. If you do for some reason use FORMAT (in access) or CONVERT (in SQL).
Check the Books online of SQL server for more information on date conversions...


Good luck,

Mark.

 

[villan60]

Thanks for the response. It seems that dates do have to be in American Format when using SQL in VB (according the the Access help).

Thus I am using FORMAT and it is working well.

Thanks
Dax

 

[therayster]

On Microsoft products dates are stored internally as a serial number and displayed according to the formats specified in the regional settings of the machine they're on. If you're talking about using a date criteria for SQL you can use almost any date format you choose by using the DATEVALUE() function. This converts a string to a serial date value and any date comparision operation has to convert dates to this format eventually anyway so you're just cutting out the middle man. If you have fields that are supposed to hold dates that currently contain non-Date values you could update the value with DateValue([field]) and convert them all to valid dates, again that are displayed according whatever format is specified in the regional settings of the machine they're on. An advantage of DateValue is you can use any valid date format (not just standard American), pass it in as a string, and it returns a serial date. This gains you a little bit of code speed. Format affects the cosmetic structure of the string but returns a Variant that then has to be turned into a serial number internally anyway.
Hope it helps...