Date format for SQL Query

bonzovt
10-14-2004, 09:31 AM
I have a table in an access database where I want to only store orders from the current date and the last two days before that. I am reading all the order information into Access from an input file, and in that file the dates are formatted as such: '10/14/04'

I was trying to write a query similar to this:
DELETE *
FROM tblHistory
WHERE ShipDate<(Date()-2);

but my shipdates are in a different format than what Date() brings up. Has anyone found a way to work around something like this?

Granty
10-14-2004, 09:36 AM
Using functions like DATEDIFF, DATEADD etc rather than using mathematical operators seem to minimise these problems.

bonzovt
10-14-2004, 10:38 AM
Using functions like DATEDIFF, DATEADD etc rather than using mathematical operators seem to minimise these problems.

so does that mean i should be able to do something like DATEDIFF(ShipDate, Date())? wouldn't i still run into formatting issues since my ShipDate is in the format 'mm/dd/yy'?

Granty
10-14-2004, 10:43 AM
Heheh its possible, I still get date issues and ive been doing this for years.

You can always use Format(MyDate, "DD/MMM/YY") or whatever format you prefer

bonzovt
10-14-2004, 10:47 AM
Heheh its possible, I still get date issues and ive been doing this for years.

You can always use Format(MyDate, "DD/MMM/YY") or whatever format you prefer

I guess my other problem is possibly that my ShipDate field is set as a text field and not as Date/Time. If I am reading in '10/14/04' from a text file, will it store that as Date/Time, or will it automatically revert back to text since that isn't any kind of Date/Time format?

MKoslof
10-15-2004, 06:59 PM
It depends on how you defined your tables :). You should be storing date time values in the proper date time data type. If you are reading in a text file, you can take the string value and convert it into a date before inserting it into your table. There are tons of options you can use such as CDate, Format, etc.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum