Re: Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache
From: Chris Barber (chris_at_blue-canoe.co.uk.NOSPAM)
Date: 02/09/04
- Next message: Aaron Bertrand - MVP: "Re: Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache"
- Previous message: Aaron Bertrand - MVP: "Re: CDONTS and sending HTML and text versions simultaneously"
- In reply to: J P Singh: "Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache"
- Next in thread: Aaron Bertrand - MVP: "Re: Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Feb 2004 18:59:50 -0000
Unless your dates are held in the DB as strings (in which case you are going
to be doing a lot of hair pulling) you should always try and pass dates in
SQL statements as ISO format:
yyyymmdd hhnnss [All databases seem to like this]
or
yyyy-mm-dd hh:nn:ss [SQL Server doesn't like this when in non English / US
language mode: http://tinyurl.com/2x7sf]
since the ISO format cannot be misconstrued to be anything other than the
date intended.
http://www.4guysfromrolla.com/webtech/tips/t022202-1.shtml
If the dates *are* stored in strings then you obviously need to cast [read
'CAST' for SQL Server and whatever works for alternative databases) them as
datetime and compare against datetime in your SQL statement. However, you
may get incorrect results because of the possibility that the cast statement
may translate the date incorrectly (eg. dd/mm/yy read as mm/dd/yy).
Chris.
"J P Singh" <noemail@asIhatespam> wrote in message
news:ehM%2361y7DHA.3860@tk2msftngp13.phx.gbl...
Hi All
I am trying to query a database with a combination of surname and date of
birth but it is giving me wrong results in certain conditions.
It is the mm/dd/yyyy and dd/mm/yyyy stuff that is not making it work.
If I enter date like 25/12/1976 then it works fine as the date will not be
valid like 12/25/1976 and everything works fine and my query executes
properly.
However if I enter a date like 07/08/1976 (07-Aug-1976) it think the query
thinks I have entered 08/07/1976 (08-July-1976) and brings back the wrong
result.
Can some one please help.
I am using the code below
strDateofBirth = request.form("dd") & "/ & request.form("mm") & "/ &
request.form("yy")
MySql="select * from empprofile where lastname ='"&strSurname&"' and
dateofbirth = #" & strDateofBirth &"#"
RS.open MySql,conn
- Next message: Aaron Bertrand - MVP: "Re: Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache"
- Previous message: Aaron Bertrand - MVP: "Re: CDONTS and sending HTML and text versions simultaneously"
- In reply to: J P Singh: "Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache"
- Next in thread: Aaron Bertrand - MVP: "Re: Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
