Date

[dummy]

Hi there!



I'm stuck!

How can I change a date format yyyymmdd text format into a yyyy-mm-dd or dd-mm-yyyy format?

Also how can I run a query to counts a period of time in between dates?

Thanks a lot!

[pbaldy]

You can use the DateSerial() function, along with the Mid() function to pull out each of the appropriate parts. You can use the DateDiff() function to calculate an interval between two dates. More info on all these functions in Help.

[dummy]

I will try it!

[pbaldy]

No problem; post back if you get stuck. Welcome to the site by the way!

[dummy]

I am still stuck on this...
the situation:

ID Name PostCode DateOfBirth ShoppingCategory ShoppingCatergory1 ShoppingDate (and lots of other fields, that don't really matter)

the problem is that: date of birth and shoppingdate come up as yyyymmdd (and some times incomplete or 0000mmdd, yyyy00dd, yyyymm00 or even 00000000) and also this has to be fixed to a standard parameter that we have.

If I use design view it does not accept to change into a date/time format...

I need to have this as dd-mm-yyyy view.

And, How can I find out the age of the person when shopping a product?

I know that I asked many things, but I really would be happy with some help!

xx

[pbaldy]

I've been on vacation; did you sort this out? How do you expect to calculate an age when records might not have the birth date?

[dummy]

Not really.

and I will have to 'set' a date for those that have a incomplete date or ignore them.

by the way: can I set a date automatically for those with invalid date?

Many thanks

[weekend00]

set invalid date to null:
update tbl set strdate=null where mid(strdate,1,4)="0000" or mid(strdate,5,2)="00" or mid(strdate,7,2)="00"

[pbaldy]

I imagine you would test for "00" or "0000" as appropriate, and replace with whatever you want the default to be. I might use different methods depending on whether you were doing this on the fly or fixing bad data permanently.