| Author |
Topic |
|
promods
Starting Member
2 Posts |
 Posted - 2004-07-09 : 11:57:31
|
| Hi,How can I find the date format of the sql server(date format) is mmddyyyy or ddmmyy?, USA or European date format.Please help me.ThanksPromod |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-09 : 12:33:21
|
| IF CONVERT(datetime, '04/05/2004') = CONVERT(datetime, '20040405') PRINT 'USA style'IF CONVERT(datetime, '05/04/2004') = CONVERT(datetime, '20040405') PRINT 'European style' |
 |
|
|
promods
Starting Member
2 Posts |
Posted - 2004-07-09 : 13:20:50
|
| Thanks for the reply.This is not I am looking for, I need to get the date format settings from the sql server. I know in Model database there is a table systypes which contains 'datetime' record. Do anyone know which item in the record represent the dateformat, eg:USA or European date format |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-09 : 13:26:36
|
| SQL Server doesn't STORE dates in any format. The default DISPLAY though is in USA format. You must use CONVERT with a style to put it into the format that you need.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-09 : 14:01:26
|
| Tara's quite right, but the way that text/string [varchar] dates are converted to datetime is influenced by the SET DATEFORMAT DMY setting - either explicitly or however SQL is currently set up - and the above test will show whether you have DMY or MDY set.But, to be honest, if the problem is with interpretation of dates in "text/string" format you'd be better off presenting them to SQL in an unambiguous format - yyyymmddKristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-09 : 23:18:21
|
| I prefer yyyymmdd for everything. It saved my hide when we were converting everything for the year 2000. Everybody always told me I was an idiot for using it and enforcing it, especially in Access and SQL Server. When 2000 rolled around, I suddenly stopped being an idiot. lol Funny how those things work.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-10 : 01:38:32
|
| You gotta go unambigous ...MeanOld's reply is 07/09/2004 - what's that? September already? I hate it ...One of my pet hates is that Americans think "12/31/2004" is going to be understood by everyone, and the Europeans think that "31/12/2004" will be Just Fine ... Quick, hands up, what month is "02/03/04"? What year? Its crazy ... Our stuff reformats all user-entered dates to "31 Dec 2004" so even though Americans would, I believe, use "Dec 31, 2004" there is no confusion about "31 Dec 2004"Perhaps I shouldn't get so worked up about all this and just marry my Sister and get on with life ...Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-10 : 10:34:40
|
| How is my reply 07/09/2004????My reply was yyyymmdd. Hello? Been smoking crack across the pond? :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-11 : 03:41:17
|
| Sorry ... no I meant the date of your post shown here in SQLTeam |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-11 : 13:53:23
|
| Ah, so to clarify, I was the one smoking crack. Thanks for clearing that up. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-11 : 14:49:04
|
 |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2004-08-05 : 11:08:39
|
| Hi, I have been working on something similar, and thought that I would share what I found:To see if the date setting is in US, all you need to type is:sp_configure 'default language'If it returns 0, then this means that the lang configuration of the server is US. If it is not, then type the following to see what it is:select name, alias, dateformatfrom syslanguages where langid = (select value from master..sysconfigures where comment = 'default language')Alternatively, you can query the syslanguages table to find out the value of the return:select langid, alias from syslanguages order by langidI know this is a little late in the day, but it could be useful in the future.Hearty head pats |
|
|
|
|
How to find sql server date format settings