Issue finding gap between two dates (text strings) in SQL

[chris-uk-lad]

Hi all,

Having a lil trouble with a query. Im trying to returns results where the difference between OldDate and "2009-05-27" is less than or equal to 20 years. The issue i have is that OldDate is a text string (import from csv), and i dont believe CDate works in SQL.

SELECT * 
FROM INFO
WHERE DateDiff('yyyy',OldDate,"2009-05-27") <=20;

Maybe just a minor thin im missing but my mind hasn't clicked to it yet.

Thanks

 

[namliam]

Hi Lad,

I am sure you know, but let me remind you... Dates need to be ## not ""
WHERE DateDiff('yyyy',OldDate,#2009-05-27#) <=20

Also note that datediff "YYYY" (or 'YYYY', think both work) only looks at the year not the actual date, thus anything 1989 will be 20 years irrespective of the actual date.
For this you would need a "age-like" calculation to make sure the exact date works as expected.

Happy to help as always your Mailman

 

[chris-uk-lad]

Thanks for the notes, to be honest i hadn't thought of the months / days ^^;

How can i format the string 19900101 into 1990/01/01 as a table value in a query? I plan on doing this then running the datediff. Have been browsing but cannot find the specific expression.

Expr1: Format([OldDate],'####/##/##)

 

[namliam]

No no no...

You need dates as input for datediff... you need to convert your ,"2009-05-27") to a date not your date to a string...

 

[Brianwarnock]

If you are just going to use Datediff("yyyy".... rather than a proper age calculation then 2009-Left(olddate,4) <=20 will give the same result.

Brian

 

[chris-uk-lad]

No no no...

You need dates as input for datediff... you need to convert your ,"2009-05-27") to a date not your date to a string...

Appreciate the input but i think a point has been missed.

At this time the date is a STRING, in the format of 19900101. I need to convert that into a date format that can be read by datedif (as CDate wont convert it in its current state) hence need to know how to format 19900101 to become 1990/01/01. Ive however hit a snag as to how i do this?

Apologies if i didnt make this seem more apparent.

 

[chris-uk-lad]

Ive solved that part now using

Expr1: CDate(Left([OldDate],4) & "/" & Mid([OldDate],5,2) & "/" & Right([OldDate],2))

Now to sort out DateDiff to the approximate time and not year ^^;

 

[namliam]

Using the left, right, mid functions you can cut up the YYYYMMDD format
Left(..,4)
Mid(...,5,2)
right(...,2)

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

 

[chris-uk-lad]

Using the left, right, mid functions you can cut up the YYYYMMDD format
Left(..,4)
Mid(...,5,2)
right(...,2)

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

much appreciated

 

[chris-uk-lad]

Right, down to a bugger now:

My query contains:

Expr1: CDate(Left([OldDate],4) & "/" & Mid([OldDate],5,2) & "/" & Right([OldDate],2))

New: CDate("27/05/2009")

Newest: Abs(DateDiff("d",[New],[Expr1])\365.25) & "Years," & Abs(DateDiff("m",[New],[Expr1]) Mod 12) & "Months," & Abs(DateDiff("d",[New],[Expr1]) Mod [New])/(365) & "Days"

Newest = "64Years,7Months,64.6931506849315Days"

Newest works out the exact difference in date in months, days and years but i cannot work this for 'less than "20"'. Any suggestions?

Sorry for dragging this on a lil :s

 

[chris-uk-lad]

nevermind, all done! thanks for the assist everyone

 

[namliam]

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

I dont know how else to stress this... so let me repeat ...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

And this is exactly why...

New: CDate("27/05/2009")

Access in general and CDate in particular work with MM/DD/YYYY, this happily (always) goes right cause 27 is not a valid month, try
Format(cdate("10/05/2009"), "dd mmm yyyy")

This will/may return 05 oct instead of 10 may

Small correction:
It works off the regional settings with CDate, not the US Format. However hardcoding something that may change per user/per machine is obviously still bad. So once again...

Then use the dateserial function (NEVER use the CDate function if you can avoid it) to convert it to a real date...

Also the \365.25, is a real "stop gap" measure LOL, how did you finaly get it to work as you want?

 

[Brianwarnock]

Since you now are going to use the Month and Day not just the year as at the begining I would code it

test for testdate-olddate <= 20years

Datediff("d",dateserial(left(olddate,4)+20,mid(olddate,5,2),right(olddate,2)),testdate)<=0,TRUE,FALSE

As Namlian said use Dateserial
.
Brian