I have got an issue with dateformats. When i retrieve a date field from the
sql server it is noted as 02/10/2005 17:40:00. everything fine so far. I
want to compare it to the current time. This doesn't work. When i print the
current datetime i get " 4 Nov 2005 5:40PM". How can i set sql so it returns
the current date as 02/10/2005 17:40:00.
Your help is much appreciated.

Regards Ron Wallegie

If you just want to get the presentation do display as you specifiied, you
can format it to "print" by converting it to a varchar.  Look up CAST and
CONVERT in BoL.

SELECT CONVERT(varchar(10), GetDate(), 101) + ' ' + CONVERT(varchar(8),
GetDate(), 108)

HTH,

John Scragg

Show quoteHide quote

Ron Wallegie (walle***@planet.nl) writes:
Preferrably you should return datetime value as-is from SQL Server, that
is no convert. The date value is then passed to the client as a binary
value, and the client will format it according to regional settings.

Same thing applies when you pass dates to SQL Server - pass them as
datetime values in parameters, and conversion will be performed by the
client API, again according to regional settings.

If you for some reason must work with date literals in SQL Server, use
the the format YYYYMMDD. This format is safe from different interpretations.
Note that SQL Server does never look at regional settings in Windows,
and has its own settings. And due to legacy, the safe format in Windows,
YYYY-MM-DD is not safe in SQL Server.

Date issue
DB Design issue
Help selecting maximum number of characters
How to calculate comparisons of fields between records
Deleting large amount of data on sql server 2000