Datediff function and date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to run an SQL to select records from a table. The dates in the table
(which is imported) are text fields in format YYYYMMDD. I need to do a
Datediff in the SQL to select records from 0-30 days old compared to today.
How does datediff interpret date formats? Help for datadiff does not specify
what format the date must be in.
 
I need to run an SQL to select records from a table. The dates in the table
(which is imported) are text fields in format YYYYMMDD. I need to do a
Datediff in the SQL to select records from 0-30 days old compared to today.
How does datediff interpret date formats? Help for datadiff does not specify
what format the date must be in.
Click to expand...


The format is irrelevant, but it must be date datatype, not text.
You first convert the text to a valid date.
Then you can use DateDiff to get the number of days between that date
and today.

Try this in a query (using your text format of YYYYMMDD).
(If the text format changes, you'll need to change the DateSerial
accordingly. Once it is a valid date datatype, the format doesn't
matter.)

Difference:DateDiff("d",DateSerial(Left([FieldA],4),Mid([FieldA],5,2),
Right([FieldA],2)),Date())

Watch out for word wrap on that line. It should be all on one line.

DateSerial converts your text value into a valid date.
DateDiff returns the value in days between that date and today.
 
Back
Top