database: extracting a date range?

[gabrielcrowe]

i'm having a problem extracting a date range from the orders table in the database. am i right in saying that the field is stored as text?

wtf?

am i going loopy, or is that possibly the most daft way to store dates?

has anyone got some sql that can ignore this little problem?

[TraceyHand]

has anyone got some sql that can ignore this little problem?

I have some cans of Stella, Gabe...will that help?

[gabrielcrowe]

lol, sadly, i only drink fine whiskey.

[TraceyHand]

tsk...not a whiskey girl myself

Stella or Vodka for me, I'm afraid..
can't help then, sorry

[Jan]

You can just compare them

ie: date1 > date2

this is because they are stored backwards YYYYMMDD.

Mines a G&T Tracey if you've got a spare one.

Regards,

[gabrielcrowe]

they arent stored in a comparable format.

sql is unable to determine the relationsship between the items in a text based field.

its fine, i did it script side, with little overhead.

[wjcampbe]

Gabe,

They are tmestamps, stored as yyyy/mm/dd hh:mm:ss

Mine's a Talsiker - straight - about 4 fl oz should do it.

[Jan]

This is the first time I have to disagree with two people at once (and I don't like whisky either) sql can compare them as strings, the arrangement of the digits makes a string compare work for the dates, no problem and they are not stored as timestamps, take a look at the date ordered field in the order table, it's a string.

Zoltan, mate ... come over here and agree with me (unless I'm wrong, then just igmore me).

Regards,

[wjcampbe]

But are we not talking about the Last Update field - which is a timestamp?

[gabrielcrowe]

in the [order] table, my access tells me that the [Date Ordered] row is of type 'text'.

but even if mysql can compare the fields, it cant use 'between' for a field that isnt date, in the intra-year extraction of a date range.

for example, 1st december 06 to 31st january 07.

unless of course i'm utterly off the mark, and mssql can understand the format and ask for the range as dates anyhow.

if so, what sql do i use to extract the example date range?

[wjcampbe]

Ah, I had the wrong field.

Gabe - you would cast the Date Ordered field text content to a date type for the range comparison.

[gabrielcrowe]

CAST();

my new best friend.

[zmagyar]

Jan, I must disagree, the whiskey is fine. :-)

[gabrielcrowe]

can anyone give me an example of cast() in operation?

i cant for the life of me get it to work as it should.

$sql = "SELECT [Date Ordered], [Order Number] FROM [Order] WHERE CAST([Date Ordered] AS DATETIME) LIKE '#$fromday/$frommonth/$fromyear#' ORDER BY [Order Sequence Number] ASC;"; 

here is what *doesnt* work. this code should select a single day.

[gabrielcrowe]

game over: the access db engine does not support the cast() method. and there is no way to convert type from the jet/odbc interface in php.

that i can find.