Re: more date problems...

-----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-----


zerbie45@xxxxxxxxx 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

.

Relevant Pages

  • Re: Error when Date/Time is a criteria
    ... When i have this query, ... This is my SQL for Query1: ... Both my table date/time fields data type are set to Date/Time and default ...
    (microsoft.public.access.queries)
  • Re: Formula with a varchar field in time format over 24 hours
    ... In the .csv file, the ... > successfully import this field into SQL with a data type ... > a different data type within a query. ...
    (microsoft.public.sqlserver.programming)
  • Re: Importing Hyperlinks
    ... My initial assumption was the data would be stored in the correct format in ... a nvarchar data type. ... I understand that SQL does not display the info the enduser, ... >> I'm assuming the data type is incorrect and I checked the documentation ...
    (microsoft.public.sqlserver.dts)
  • Re: My query is broken and I cant fix it :<(
    ... guess at the data type. ... If I do a straight query on the OR_Number and the date, ... bogus times does not pull. ... the SQL because its doing the SELECT and FROM portions ok. ...
    (microsoft.public.access.queries)
  • Re: INSERT SQL to append recs frm another Table, NULL DATE append 30/1
    ... Suggest you post the SQL text of the Insert query and the data type of the ... (Possibly unneeded instructions follow) ...
    (microsoft.public.access.modulesdaovba)