TIMESTAMP field problem when moving to another server 2

[vias]

vias (Programmer) Mar 7, 2005
I have transfered a database from a server to another.
One of the tables contains a timestamp field. The value
of one of the timestamp fields was "3/6/2005 3:00:00 PM".
After the move the value of the same field is now
"06/03/2005 15:00".

In my program I have the following code:

Dim timestamp
timestamp = rs("timestamp")
"select strdate = datediff(day, '"&timestamp&"', getdate())"

This SELECT statement was working fine on the previous server.On the new server, I get problems with some values; for instance, I get the following error when the timestamp value is "28/02/2005 16:58:00"
-----------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
data type to a datetime data type
resulted in an out-of-range datetime value.
-----------------------------------------------------------

Thanks in advance for your help.

Vias

 

[SQLBill]

SQL Server does NOT store datetimes in any specific format. It DOES display the datetime based on the system settings and/or the SQL Server collation settings.

To get SQL Server to understand your values, include SET DATEFORMAT in your scripts. This is used to tell SQL Server how to interpret datetime values.

EX. SET DATEFORMAT MDY
<rest of script>

SET DATEFORMAT DMY
<rest of script>

Refer to the BOL for more information on DATETIME and DATEFORMAT.

-SQLBill

Posting advice: FAQ481-4875

 

[ton12]

I have a similar problem with dates.

I'm new to ASP, I would like to know
how to include 'set dateformat mdy' in my
ASP code.

Thanks.

 

[vongrunt]

SQL Server database has one collation, ASP/IIS another locale settings. When these two don't match, date constants in dynamic SQL may cause statement to crash or swap date parts. The safest shot is to write wrapper function for formatting dates:

Function dbDate( dDate )
	Dim vOldLocale: vOldLocale = SetLocale("en-gb")
	dbDate = formatDateTime( dDate, vbGeneralDate )
	SetLocale(vOldLocale)
End Function

... and then use it every time you have to glue dates to strings. You can modify this function to return dates in non-ambiguous format like ISO or canonical ODBC. This should work on any server installation.

FYI: also check ASP/VBS documentation for LCID chart and Session("LCID") thing.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]