Help ! Date problem !

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
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
|||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
David Gugick wrote:
> bthouin wrote:
>
> 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
>
|||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
|||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:
>
> 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.
>
|||bthouin wrote:[vbcol=seagreen]
> 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:
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.
[vbcol=seagreen]
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
David Gugick wrote:
> bthouin wrote:
>
> 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.
>