Sunday, March 11, 2012

Datetime format regardless regional settings

Hi,

in tsql there is a common format for datetime (regardless regional settings)?

I use : 'mm/dd/yyyy' to access cols in database,

but someone says that the right one is: yyyy-mm-dd.

Any suggestion about that?

Thank a lot

I believe you want the ISO format -- 112; that is yymmdd. Can somebody double check this?


Dave

|||

yes dave.. 112 is the ansi unseperated date format.....

so best option is to get all ur date in this format: select convert(varchar, getdate(), 112)

|||

Hi,

I tried thid on nortwind DB (date are: dd/mm/yyyy)

select * from orders where orderdate < '19960704'

this does work, no data are retrieved,

but works for:

select * from orders where orderdate < '07/04/1996'

select * from orders where orderdate < '1996-07-04'

both select one row (if nortwind is the original one)

?

any suggestion?

All extract the same regardless the regional settings?

|||

Fast:

I tried all three queries against my copy of the northwind database. The lowest date that I have in the orders table is the date '07/04/1996'. For this reason, I do not get any rows returned with any of the select statements. However, when I change the "less than" operator to a "less than or equal to" operator I get the expected row. I am not getting the same results as you. I am afraid I can't help on this one. Sorry fo adding to confusion.


Dave

|||

hmm..works fine with me...the lowest date in northwind is..1996-07-04 00:00:00.000..

i get 0 rows for all the queries u mentiones...and 1 each when i use <= ..

neways...wat do u want to achieve exactally....see 112 is the ansi date format, which shud be used for date conversion and comparisons as its the standard....select convert(varchar(10), getdate(), 112) ...

yyyy-mm-dd +time is how sql server present it as..

|||

Sorry for having written too fast,

all three select retrieve 1 record if date is minor than 5 july 1996,

I have tried all queries with several regional settings and everything work.

If anyone has other suggestion, is welcome

Besides in internet there are this interestings articles:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

and

http://www.karaszi.com/SQLServer/info_datetime.asp

Thank

No comments:

Post a Comment