Datediff function and date format


M

mscertified

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

Advertisements

F

fredg

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.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Datediff Function 1
Weekday only in Datediff function ? 4
DateDiff 5
datediff 5
Conditional Formatting with DateDiff (Access 2007) 0
DATEDIFF - THE TRUTH 1
DateDiff on Several days 2
Counting days using DateDiff 2

Top