That was my idea ;) A stopgap solution until he fixes his localisation
issue.
Tony
Work
----- Original Message -----
From: "David Smart"; < smartware%40optusnet.com.au">smartwareoptusnet.com.au>
To: < ASP-Support-Group%40yahoogroups.com">ASP-Support-Groupyahoogroups.com>
Sent: Monday, February 19, 2007 9:20 AM
Subject: Re: -=(ASP-Support-Group)=- asp/sql date issue
>I wasn't suggesting converting the date fields to varchars. (Sorry - I
> probably gave the wrong impression from what I said.) It's simply a
> matter
> of how you specify dates in SQL statements.
>
> Leave the dates in the database as date format.
>
> When you put a date into an SQL statement, format it to be YYYY-MM-DD.
>
> My VBScript is a bit rusty, but something like
>;
> sql = "SELECT * FROM [tblSubLinks] WHERE Active = 1 AND (dateLeaveHome IS
> NOT NULL) " & _
> "AND dateProgramStart > '" & format (now () - 1, "YYYY-MM-DD";) &
> "' ORDER BY dateProgramStart";
>
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "Moshe Tapnack" < elists%40tapnack.com">eliststapnack.com>
> To: < ASP-Support-Group%40yahoogroups.com">ASP-Support-Groupyahoogroups.com>
> Sent: Monday, February 19, 2007 3:16 PM
> Subject: RE: -=(ASP-Support-Group)=- asp/sql date issue
>
>
>> Thanks David and Tony,
>>
>> Unfortunately server setup is my weak side :(
>>
>> In the meantime, I have added the getdate() to the SQL string, which
>>; seems
>> to have solved the issue for now. But varchars sounds like the long term
>;> solution.
>>
>> Thanks again!
>> Moshe
>>
>>
>> -----Original Message-----
>> From: ASP-Support-Group%40yahoogroups.com">ASP-Support-Groupyahoogroups.com
>> [mailto: ASP-Support-Group%40yahoogroups.com">ASP-Support-Groupyahoogroups.com] On Behalf Of David Smart
>> Sent: Sunday, February 18, 2007 10:46 PM
>> To: ASP-Support-Group%40yahoogroups.com">ASP-Support-Groupyahoogroups.com
>> Subject: Re: -=(ASP-Support-Group)=- asp/sql date issue
>>
>> Databases notoriously do not handle "UK" date format - this is probably
>> true
>;>
>;> of SQL Server too. Convert all dates to international format -
>>; YYYY-MM-DD
>> in SQL statements so there will be no ambiguity to computer or human.
>>
>> You shouldn't have problems with that date format.
>>
>> The other alternative is to let the database engine supply the date (i.e.
>> put the call to the date function inside the SQL string).
>>
>> Regards, Dave S
>>
>> ----- Original Message -----
>>; From: "Moshe Tapnack" < elists%40tapnack.com">eliststapnack.com>
>> To: < ASP-Support-Group%40yahoogroups.com">ASP-Support-Groupyahoogroups.com>
>> Sent: Monday, February 19, 2007 3:34 AM
>> Subject: -=(ASP-Support-Group)=- asp/sql date issue
>>
>>
>>> hey folks
>>>
>>> I have an ASP page, with an SQL Server 2000 backend.
>>> the DB has a collation of Hebrew_CI_AS
>>>
>>> when I request records as follows:
>>>
>>> SELECT * FROM [tblSubLinks] WHERE Active = 1 AND (dateLeaveHome IS NOT
>>> NULL)
>>> AND dateProgramStart > '" & now()-1 & "' ORDER BY dateProgramStart
>>>
>>> I get the following error, when dateProgramStart = 27/02/07 12:00:00 AM
>>> :
>>> The conversion of a char data type to a datetime data type resulted in
>>;> an
>>> out-of-range datetime value.
>>>
>>> Which points to the spot where I am comparing to yesterday's date.
>>>
>>> In SQL Management Studio, I see the dates as dd/mm/yyyy - as should be
>>;> as
>>> defined (I think) by the Hebrew collation.
>>>
>>> I have found, that when I manually change the page LCID to that of an
>>> american one (1033) - I do not get that error.
>>>
>>> but when i have the LCID as Hebrew (or UK), as i want it, i get the
>>;> error
>>> above.
>>>
>>> so the data in the DB seems like its in the correct format, but when
>;>> accessed with the relevant LCID from ASP, it errors. only with the
>>> American
>>> LCID (incorrect for this situation) - it errors.
>>>;
>>;> any ideas???
>>>
>>> Thanks!
>>>; Moshe
>>>
>>>
>>>
>>>
>>> Group homepage is here:
>>> http://groups.yahoo.com/
