Dates mm/dd/yyyy & dd/mm/yyyy giving a major headache

[J P Singh]

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

 

[Bob Barrows [MVP]]

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.

Searchwww.aspfaq.com for the keyword "dates". This is a VERY frequently
asked question

Bob Barrows

 

[Aaron Bertrand - MVP]

If I enter date like 25/12/1976

Why do people insist on using ambiguous date formats?

However if I enter a date like 07/08/1976

I don't know if that's July 8th or August 7th. How do you expect software
to?

Use a *STANDARD* format (that's why we have standards).

Ironically, the standard changes between products. For Access, the ONLY
safe date format is YYYY-MM-DD. (For SQL Server, it's YYYYMMDD.)

strDateofBirth = request.form("dd") & "/ & request.form("mm") & "/ &
request.form("yy")

So all you have to do is change the above to:

strDateofBirth = request.form("yy") & "-" & request.form("mm") & "-" &
request.form("dd")

Assuming, of course, that you've already validated that this creates a valid
date and that all three values are numbers and don't contain "danger"
characters like '

 

[Chris Barber]

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

http://www.aspfaq.com/2206

Chris.

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

 

[J P Singh]

Thanks a lot everyone.

Why do people insist on using ambiguous date formats?


I don't know if that's July 8th or August 7th. How do you expect software
to?

Use a *STANDARD* format (that's why we have standards).

Ironically, the standard changes between products. For Access, the ONLY
safe date format is YYYY-MM-DD. (For SQL Server, it's YYYYMMDD.)


So all you have to do is change the above to:

strDateofBirth = request.form("yy") & "-" & request.form("mm") & "-" &
request.form("dd")

Assuming, of course, that you've already validated that this creates a valid
date and that all three values are numbers and don't contain "danger"
characters like '