By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,495 Members | 1,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,495 IT Pros & Developers. It's quick & easy.

more date problems...

P: n/a
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

Mar 1 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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

Mar 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.