Asp classic, How to select a string date which is stored as a string in database text field?

Question

The date stored as string in database. I cant select between two dates or make "ORDER BY date_field DESC"

sql="SELECT * FROM payments where p_dat Between #"&from_d&"# AND #"&to_d&"# ORDER BY p_dat DESC"

but the p_dat is a text field not Date/Time in database.

I want to do that without changing the date field to Date/Time. Thank you

Convert the string to a `datetime` first in a sub query then do the compare. What database are you using? — user692942, Dec 20 '16 at 15:17
please would you explain how to do that. using access db. thank you — Massry, Dec 21 '16 at 08:29
Possible duplicate of [Convert String to Date in MS Access Query](http://stackoverflow.com/questions/17361338/convert-string-to-date-in-ms-access-query) — user692942, Dec 21 '16 at 14:08

score 2 · Accepted Answer · answered Dec 21 '16 at 13:16

2

Access database SQL has CDate() function available to it.

sql="SELECT * FROM payments where Cdate(p_dat) Between #" & from_d & "# AND #" & to_d & "# ORDER BY Cdate(p_dat) DESC"

I have tested this on an Office 2007 database.

answered Dec 21 '16 at 13:16

Brian T

nicely done, exactly what I meant in my answer above – tamak Dec 21 '16 at 13:37
It entirely depends, how the date *string* is structured and whether `CDate()` can convert it to a real date. – user692942 Dec 21 '16 at 14:07
That's what I'm looking for. thank you very very much. u r a prince. – Massry Dec 21 '16 at 14:08

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

You can wrap the databases' text field values in CDate(), in the SQL Select statement, to convert them to dates for comparison.

edited May 23 '17 at 11:53

Community

answered Dec 20 '16 at 15:55

tamak

The OP is asking to compare as a date in the query when the data is stored as a string in the database not convert a string to a date in Classic ASP / VBScript. – user692942 Dec 20 '16 at 16:33
What's stored in the database is NOT a date/time object... using CDATE() will convert it to a date/time object, thereby making it available for date comparison. As the answer below states, you can use CDate() in Access database SQL statement/query. – tamak Dec 21 '16 at 13:41
I didn't say it was, what I said was *"when the data is stored as a string"*. This was all before the OP confirmed they were using [tag:ms-access] anyway. My comment was aimed at the [linked answer](http://stackoverflow.com/questions/8710309/how-to-convert-string-to-datetime-format-classic-asp). – user692942 Dec 21 '16 at 14:05

2 Answers2