Matching a text to a date string

[BradYB]

Hi,

I have a database which shows a date in the format dd/mm/yyyy.

For a very longwinded reason, however, the date field is actually text (all to do with the way I converted my date field to get the info I wanted from SQLServer 2000).

Is there a way I can extract data from the table but limit this data so that only records from today will be seen, despite the date field i'm searching on being a text field rather than date/time ?

Thanks in advance,
BradYB

[oracleguy]

I think I get what your asking...


SELECT * FROM tblBlah WHERE convert(datetime,YourDateField)='1/23/2004'


You mean something like this? I think that syntax is correct, but I didn't test it.

[BradYB]

Thank you, i tried that. However, it fails with the below message. I think because the string has slashes (/) in it:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

part of code:

todaysdate = Date()

GROUP BY USERMN,EDITDATEINT HAVING convert(datetime,EDITDATEINT) = '" & todaysdate & "';"

Thanks,
BradYB

[BradYB]

Do you think it would work if i was to try something like this (obviously with the correct syntax):

cday = 1st and 2nd character of todaysdate
cmonth = 4ht and 5th character of todaysdate
cyear = 7th and 8th and 9th and 10th character of todaysdate

eday = 1st and 2nd character of EDITDATEINT
emonth = 4th and 5th character of EDITDATEINT
eyear = 7th and 8th and 9th and 10th character of EDITDATEINT


then put WHERE (or HAVING) clauses to say cday = eday AND cmonth = emonth AND eyear = cyear

???
BradYB

[glenngv]

The Convert function has this syntax:

CONVERT(expression, type)

[whammy]

BradyB, something like your idea above should work if it's a text field. Just don't forget to add '/' in between.

A good thing to do when making sure you're formatting your SQL statements correctly (also when debugging!) is:

SQL = "SELECT * FROM Whatever" 'For example
Response.Write(SQL) : Response.End

That way you can print your SQL out directly to the page, and try the resulting statement in SQL Query Analyzer. :)

[glenngv]

Originally posted by glenngv
The Convert function has this syntax:

CONVERT(expression, type)

I pointed this out because BradYB is using

convert(datetime,EDITDATEINT)

where it should be

convert(EDITDATEINT, datetime)

[oracleguy]

Originally posted by glenngv
I pointed this out because BradYB is using

convert(datetime,EDITDATEINT)

where it should be

convert(EDITDATEINT, datetime)

Yeah I wasn't sure... Thanks for correcting me.