more date problems...

[zerbie45]

I have a high number of computers that at logon write some information
to a sql 2005 database. Information such as computer name, user name,
logon date and logon time are entered.

Because computers use different regional options, I notice that queries
to this database return inconsistent results due to different date
formatting. For example I see computers entering 1/3/2006 and 1/3/6 or
1/3/06.

How can I modify my query so that it reformats the date. This is my
current query I execute from within an ASP application:

RS.Open "Select * from PCLogs.dbo.logs WHERE Note = '" &
Request.Form("date") & "' ", dbConn, 1

The date is a variable that refers to a dd/mm/yyyy format. The date
column is of type text.

I'm a novice in SQL so any help would be greatly appreciated !
TIA and Regards

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The date column should not be a "text" column (I assume you mean a
VARCHAR column). It should be a Date data type column. Change that, if
you can.

I don't know if VBScript has the Format() function, but try that. E.g.:

Format(Request.Form("date"),"YYYYMMDD")

This will format the date in a format that SQL understands.

If you can't change the data type of the column you should be using a
stored procedure (SP) to save the data into the table. The SP should
format the date data to a default format, preferrably YYYYMMDD, that the
VBScript command can "know" to use when querying the table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAYOA4echKqOuFEgEQJ51wCfdi5FGvlY/cT7wCe6qLzaciAya7IAoNdh
WjXzm/NNtiUAJdhiVpFCZTMh
=c6LY
-----END PGP SIGNATURE-----
ze******@gmail.com wrote:

I have a high number of computers that at logon write some information
to a sql 2005 database. Information such as computer name, user name,
logon date and logon time are entered.

Because computers use different regional options, I notice that queries
to this database return inconsistent results due to different date
formatting. For example I see computers entering 1/3/2006 and 1/3/6 or
1/3/06.

How can I modify my query so that it reformats the date. This is my
current query I execute from within an ASP application:

RS.Open "Select * from PCLogs.dbo.logs WHERE Note = '" &
Request.Form("date") & "' ", dbConn, 1

The date is a variable that refers to a dd/mm/yyyy format. The date
column is of type text.

I'm a novice in SQL so any help would be greatly appreciated !
TIA and Regards