| Groups | Blog | Home |
sql server clients : Help ! Date problem !
bthouin wrote: > Hi, > > I am experiencing a totally unexplainable date behavior, coming out of > the blue, without warning, I don't understand anything any more. > > Let me explain: I am using Dreamwaever MX 2004, ASP, VBScript, and > SQLServer 2000 as DB. I am developing since a while an application, > and I am nearly finished with the most complicated bit of it. Until > this > afternoon, everything worked fine, and I had no particular problems, > neither with data nor with dates. > > Today, I was adding some harmless code to retrieve some records from > the database, and suddenly, my app, and SQLServer Enterprise Manager, > began to behave VERY WEIRDLY on D A T E S. > > a) in Enterprise Manager, without me doing anything, the date format > changes from Swiss (dd.mm.yyyy) to US (m/d/yyyy) ! > > b) Not only that, but the regional date settings is also changed to US > date ! > > c) a SQL Statement that was finding a row in the DB does NOT find it > anymore, because on of the attributes it uses is a date, and the > format stuff above screws up the access > > d) a central simple routine of mine that converts dates from the Swiss > format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS working and > gives me an error. If I then change my date regional settings back to > Swiss, the routine starts working again ! But the code in it is like > this: DToutSQL = Year(DTIn) & leadZeros(Month(DTin)) & > leadZeros(Day(DTin)) where DTIn is the date in Swiss format, and > leadZeros just adds a leading zero to 1-digit month or day numbers. > The input is a string > "01.03.2005", and the test IsDate(DTIn) fails ! > > e) no matter what I do, my SQLStatement cannot find the row that I see > in the DB, but when I copy the statement to Enterprise Manager and run > it, the DB, after a long thinking pause, finds it, but switches again > the format to US !!! Then my central routine conks out again, i.e. my > app fails ! > > > Has ANYBODY any idea what's going on ? I'm currently scanning my > machine for viruses, but it seems clean. What can I do to sort out > once and for all all these stupid date problems, and to avoid the > current situation ??? > Thanks > Bernard Thouin > Zurich, Switzerland There are only two supported formats for date comparisons in SQL Server. Either will prevent the locale from interfering with date comparisons: yyyy-mm-ddThh:mm:ss.mmm yyyymmdd -- David Gugick Quest Software www.imceda.com www.quest.com
[quoted text, click to view]
bthouin wrote: > Hi David, > > Well, I'm using ONLY the 2nd one (yyyymmdd), and religiously througout > the app, that's what my central routine is all about, and that's why I > don't understand why suddenly everything fails. Interpretation of character string dates is affected by the SET DATEFORMAT setting for the connection. But this does not affect the display format. The display format comes from your local Windows settings (see Regional and Language Settings in Control Panel). Regading your other issues" c) a SQL Statement that was finding a row in the DB does NOT find it anymore, because on of the attributes it uses is a date, and the format stuff above screws up the access If you are using the correct format, it will work. My guess is the problem stems from item (d) below. d) a central simple routine of mine that converts dates from the Swiss format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS working and gives me an error. If I then change my date regional settings back to Swiss, the routine starts working again ! But the code in it is like this: DToutSQL = Year(DTIn) & leadZeros(Month(DTin)) & leadZeros(Day(DTin)) where DTIn is the date in Swiss format, and leadZeros just adds a leading zero to 1-digit month or day numbers. The input is a string "01.03.2005", and the test IsDate(DTIn) fails ! The problem is that the incoming format "01.03.2005" is not in a universal format and depending on what DATEFORMAT settings are on the client, the date interpretation can fail (Jan 3, 2005 or March 1, 2005). You should always use YYYYMMDD to start with or stay away from the DAY, MONTH, YEAR functions and parse manually using SUBSTRING. -- David Gugick Quest Software www.imceda.com www.quest.com
Hi,
I am experiencing a totally unexplainable date behavior, coming out of the blue, without warning, I don't understand anything any more. Let me explain: I am using Dreamwaever MX 2004, ASP, VBScript, and SQLServer 2000 as DB. I am developing since a while an application, and I am nearly finished with the most complicated bit of it. Until this afternoon, everything worked fine, and I had no particular problems, neither with data nor with dates. Today, I was adding some harmless code to retrieve some records from the database, and suddenly, my app, and SQLServer Enterprise Manager, began to behave VERY WEIRDLY on D A T E S. a) in Enterprise Manager, without me doing anything, the date format changes from Swiss (dd.mm.yyyy) to US (m/d/yyyy) ! b) Not only that, but the regional date settings is also changed to US date ! c) a SQL Statement that was finding a row in the DB does NOT find it anymore, because on of the attributes it uses is a date, and the format stuff above screws up the access d) a central simple routine of mine that converts dates from the Swiss format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS working and gives me an error. If I then change my date regional settings back to Swiss, the routine starts working again ! But the code in it is like this: DToutSQL = Year(DTIn) & leadZeros(Month(DTin)) & leadZeros(Day(DTin)) where DTIn is the date in Swiss format, and leadZeros just adds a leading zero to 1-digit month or day numbers. The input is a string "01.03.2005", and the test IsDate(DTIn) fails ! e) no matter what I do, my SQLStatement cannot find the row that I see in the DB, but when I copy the statement to Enterprise Manager and run it, the DB, after a long thinking pause, finds it, but switches again the format to US !!! Then my central routine conks out again, i.e. my app fails ! Has ANYBODY any idea what's going on ? I'm currently scanning my machine for viruses, but it seems clean. What can I do to sort out once and for all all these stupid date problems, and to avoid the current situation ??? Thanks Bernard Thouin
Hi David,
Well, I'm using ONLY the 2nd one (yyyymmdd), and religiously througout the app, that's what my central routine is all about, and that's why I don't understand why suddenly everything fails. Bernard [quoted text, click to view] David Gugick wrote:
> bthouin wrote: > >> Hi, >> >> I am experiencing a totally unexplainable date behavior, coming out of >> the blue, without warning, I don't understand anything any more. >> >> Let me explain: I am using Dreamwaever MX 2004, ASP, VBScript, and >> SQLServer 2000 as DB. I am developing since a while an application, >> and I am nearly finished with the most complicated bit of it. Until this >> afternoon, everything worked fine, and I had no particular problems, >> neither with data nor with dates. >> >> Today, I was adding some harmless code to retrieve some records from >> the database, and suddenly, my app, and SQLServer Enterprise Manager, >> began to behave VERY WEIRDLY on D A T E S. >> >> a) in Enterprise Manager, without me doing anything, the date format >> changes from Swiss (dd.mm.yyyy) to US (m/d/yyyy) ! >> >> b) Not only that, but the regional date settings is also changed to US >> date ! >> >> c) a SQL Statement that was finding a row in the DB does NOT find it >> anymore, because on of the attributes it uses is a date, and the >> format stuff above screws up the access >> >> d) a central simple routine of mine that converts dates from the Swiss >> format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS working and >> gives me an error. If I then change my date regional settings back to >> Swiss, the routine starts working again ! But the code in it is like >> this: DToutSQL = Year(DTIn) & leadZeros(Month(DTin)) & >> leadZeros(Day(DTin)) where DTIn is the date in Swiss format, and >> leadZeros just adds a leading zero to 1-digit month or day numbers. >> The input is a string >> "01.03.2005", and the test IsDate(DTIn) fails ! >> >> e) no matter what I do, my SQLStatement cannot find the row that I see >> in the DB, but when I copy the statement to Enterprise Manager and run >> it, the DB, after a long thinking pause, finds it, but switches again >> the format to US !!! Then my central routine conks out again, i.e. my >> app fails ! >> >> >> Has ANYBODY any idea what's going on ? I'm currently scanning my >> machine for viruses, but it seems clean. What can I do to sort out >> once and for all all these stupid date problems, and to avoid the >> current situation ??? >> Thanks >> Bernard Thouin >> Zurich, Switzerland > > > There are only two supported formats for date comparisons in SQL Server. > Either will prevent the locale from interfering with date comparisons: > > yyyy-mm-ddThh:mm:ss.mmm > > yyyymmdd >
OK, David, 3 more questions/statements:
1) Can you tell me how do I find out about the DATEFORMAT setting ? 2) >>You should always use YYYYMMDD to start with or stay away from the DAY, MONTH, YEAR functions and parse manually using SUBSTRING<< That sounds like a good idea, I'll do that. The problem is that I am using a language code page id (Session.LCID = 2055) in my code, and that shows me the dates directly in Swiss format, when I pull them out of the DB, so I thought the Day, Month, and Year functions would work fine on these dates, and they did until yesterday ! 3) Is it "normal", that, after qerying the DB in Enterprise Manager using "... where mydate = '20050301' ...", the Windows regional date settings are changed (from Swiss) to US date format (m/d/yyyy), and the Enterprise Manager displays dates also in US format ??? Or is it a bug of Entreprise Manager (Version 8.0) ? Regards Bernard [quoted text, click to view] David Gugick wrote:
> bthouin wrote: > >> Hi David, >> >> Well, I'm using ONLY the 2nd one (yyyymmdd), and religiously througout >> the app, that's what my central routine is all about, and that's why I >> don't understand why suddenly everything fails. > > > Interpretation of character string dates is affected by the SET > DATEFORMAT setting for the connection. But this does not affect the > display format. The display format comes from your local Windows > settings (see Regional and Language Settings in Control Panel). > > Regading your other issues" > > c) a SQL Statement that was finding a row in the DB does NOT find it > anymore, because on of the attributes it uses is a date, and the format > stuff above screws up the access > > If you are using the correct format, it will work. My guess is the > problem stems from item (d) below. > > > d) a central simple routine of mine that converts dates from the Swiss > format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS working and > gives me an error. If I then change my date regional settings back to > Swiss, the routine starts working again ! But the code in it is like this: > DToutSQL = Year(DTIn) & leadZeros(Month(DTin)) & leadZeros(Day(DTin)) > where DTIn is the date in Swiss format, and leadZeros just adds a > leading zero to 1-digit month or day numbers. The input is a string > "01.03.2005", and the test IsDate(DTIn) fails ! > > The problem is that the incoming format "01.03.2005" is not in a > universal format and depending on what DATEFORMAT settings are on the > client, the date interpretation can fail (Jan 3, 2005 or March 1, 2005). > You should always use YYYYMMDD to start with or stay away from the DAY, > MONTH, YEAR functions and parse manually using SUBSTRING. >
[quoted text, click to view]
bthouin wrote: > OK, David, 3 more questions/statements: > > 1) Can you tell me how do I find out about the DATEFORMAT setting ? > > 2) >>You should always use YYYYMMDD to start with or stay away from > the DAY, MONTH, YEAR functions and parse manually using SUBSTRING<< > That sounds like a good idea, I'll do that. The problem is that I am > using a language code page id (Session.LCID = 2055) in my code, and > that shows me the dates directly in Swiss format, when I pull them > out of the DB, so I thought the Day, Month, and Year functions would > work fine on these dates, and they did until yesterday ! > > 3) Is it "normal", that, after qerying the DB in Enterprise Manager > using "... where mydate = '20050301' ...", the Windows regional date > settings are changed (from Swiss) to US date format (m/d/yyyy), and > the Enterprise Manager displays dates also in US format ??? Or is it > a bug of Entreprise Manager (Version 8.0) ? > > Regards > Bernard > > David Gugick wrote: >> bthouin wrote: >> >>> Hi David, >>> >>> Well, I'm using ONLY the 2nd one (yyyymmdd), and religiously >>> througout the app, that's what my central routine is all about, and >>> that's why I don't understand why suddenly everything fails. >> >> >> Interpretation of character string dates is affected by the SET >> DATEFORMAT setting for the connection. But this does not affect the >> display format. The display format comes from your local Windows >> settings (see Regional and Language Settings in Control Panel). >> >> Regading your other issues" >> >> c) a SQL Statement that was finding a row in the DB does NOT find it >> anymore, because on of the attributes it uses is a date, and the >> format stuff above screws up the access >> >> If you are using the correct format, it will work. My guess is the >> problem stems from item (d) below. >> >> >> d) a central simple routine of mine that converts dates from the >> Swiss format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS >> working and gives me an error. If I then change my date regional >> settings back to Swiss, the routine starts working again ! But the >> code in it is like this: DToutSQL = Year(DTIn) & >> leadZeros(Month(DTin)) & leadZeros(Day(DTin)) where DTIn is the date >> in Swiss format, and leadZeros just adds a leading zero to 1-digit >> month or day numbers. The input is a string >> "01.03.2005", and the test IsDate(DTIn) fails ! >> >> The problem is that the incoming format "01.03.2005" is not in a >> universal format and depending on what DATEFORMAT settings are on the >> client, the date interpretation can fail (Jan 3, 2005 or March 1, >> 2005). You should always use YYYYMMDD to start with or stay away >> from the DAY, MONTH, YEAR functions and parse manually using >> SUBSTRING. You can find the current value using DBCC USEROPTIONS. However, I would not rely on the setting and would just avoid the DAY/MONTH/YEAR functions since you are using a non-standard date format. Just parse the string manually instead if you know the incoming format. For example, use SUBSTRING(@datevar, 1, 2) + SUBSTRING(@datevar, 4, 2) + ... to create the date. -- David Gugick Quest Software www.imceda.com www.quest.com
Hi David,
Well, I found the problem... entirely my fault. My SQL statement was allright, but NOT the way I was executing it... A hasty copy+paste left me with a messy query routine that had some rests of a ADODB.Command logic in it ! So I thought I wasn't finding the rows in the DB, but that was not really the case. So much for that. [quoted text, click to view] >>...since you are using a non-standard date format...<< What ? I'm just using a typical European format (just like the UK format, but with dots instead of slashes), which in any case makes much more sense than the insane US format, which defies any rule of the most basic logic. At least, SQLServer has the most logical format. If only Americans could accept that... Anyway, thanks for your help, and sorry for the time spent. Regards Bernard [quoted text, click to view] David Gugick wrote:
> bthouin wrote: > >> OK, David, 3 more questions/statements: >> >> 1) Can you tell me how do I find out about the DATEFORMAT setting ? >> >> 2) >>You should always use YYYYMMDD to start with or stay away from >> the DAY, MONTH, YEAR functions and parse manually using SUBSTRING<< >> That sounds like a good idea, I'll do that. The problem is that I am >> using a language code page id (Session.LCID = 2055) in my code, and >> that shows me the dates directly in Swiss format, when I pull them >> out of the DB, so I thought the Day, Month, and Year functions would >> work fine on these dates, and they did until yesterday ! >> >> 3) Is it "normal", that, after qerying the DB in Enterprise Manager >> using "... where mydate = '20050301' ...", the Windows regional date >> settings are changed (from Swiss) to US date format (m/d/yyyy), and >> the Enterprise Manager displays dates also in US format ??? Or is it >> a bug of Entreprise Manager (Version 8.0) ? >> >> Regards >> Bernard >> >> David Gugick wrote: >> >>> bthouin wrote: >>> >>>> Hi David, >>>> >>>> Well, I'm using ONLY the 2nd one (yyyymmdd), and religiously >>>> througout the app, that's what my central routine is all about, and >>>> that's why I don't understand why suddenly everything fails. >>> >>> >>> >>> Interpretation of character string dates is affected by the SET >>> DATEFORMAT setting for the connection. But this does not affect the >>> display format. The display format comes from your local Windows >>> settings (see Regional and Language Settings in Control Panel). >>> >>> Regading your other issues" >>> >>> c) a SQL Statement that was finding a row in the DB does NOT find it >>> anymore, because on of the attributes it uses is a date, and the >>> format stuff above screws up the access >>> >>> If you are using the correct format, it will work. My guess is the >>> problem stems from item (d) below. >>> >>> >>> d) a central simple routine of mine that converts dates from the >>> Swiss format dd.mm.yyyy to the SQLServer format yyyymmdd STOPS >>> working and gives me an error. If I then change my date regional >>> settings back to Swiss, the routine starts working again ! But the >>> code in it is like this: DToutSQL = Year(DTIn) & >>> leadZeros(Month(DTin)) & leadZeros(Day(DTin)) where DTIn is the date >>> in Swiss format, and leadZeros just adds a leading zero to 1-digit >>> month or day numbers. The input is a string >>> "01.03.2005", and the test IsDate(DTIn) fails ! >>> >>> The problem is that the incoming format "01.03.2005" is not in a >>> universal format and depending on what DATEFORMAT settings are on the >>> client, the date interpretation can fail (Jan 3, 2005 or March 1, >>> 2005). You should always use YYYYMMDD to start with or stay away >>> from the DAY, MONTH, YEAR functions and parse manually using >>> SUBSTRING. > > > You can find the current value using DBCC USEROPTIONS. However, I would > not rely on the setting and would just avoid the DAY/MONTH/YEAR > functions since you are using a non-standard date format. Just parse the > string manually instead if you know the incoming format. For example, > use SUBSTRING(@datevar, 1, 2) + SUBSTRING(@datevar, 4, 2) + ... to > create the date.
Don't see what you're looking for? Try a search.
|
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |