Date formatting issue...

[cwilkey]

Greetings...I have a little problem here. I have a database field that holds a date formatted as such: mm/dd/yyyy. The database field is type VARCHAR not DATE...I originally did this due to formatting purposes however now its bitting me back. I'm running the following query:

SELECT DISTINCT ID,Date_Requested,advertiser_name,Station,terms,credit_approve,assigned_date,assigned_no FROM Results WHERE Date_Requested BETWEEN '12/15/2005' AND '01/04/2006'

It doesn't seem to recognize them as dates, which only makes sense because they are VARCHAR data types.

My question is there an easy why either to convert the field type from VARCHAR to DATE while preserving the data OR can I create a function that somehow recognizes the strings as type DATE?

[abhish_daga]

your problem is coverting varchar to date format .write query like this

SELECT DISTINCT ID,Date_Requested,advertiser_name,Station,terms,credit_approve,assigned_date,assigned_no FROM Results WHERE convert(Date_Requested,datetime) BETWEEN '12/15/2005' AND '01/04/2006'

[zingmatter]

Alternately you could try changing the data type on the table using an ALTER query. If you're using SQL Server there may be a stored procedure to do this. A more convoluted approach might be to add an addition field on your table defined as date type, write all the dates across to the new field. (remember to query dates inside # marks too).

[dev.amit]

Hey friends i dont know much but can we use the isdate function of vbscript after getting the result from the table

[JoeyD]

I am pretty sure that after getting the date range out of the table you can use cdate(rst("dateField")) to change it to a date format. However if you are quering the dates for a range then the best solution is probably what abhish_daga posted. If the database field has more than just date values stored the you could do something like this to convert you dates.

If isdate(rst("dateField") then cdate(rst("datefield"))

[zingmatter]

I would suggest getting your data format in your database sorted now. While the above solutions will work, they are essentially hacks for fixing a problem that ought not be there in the first place.

[russell_g_1]

agreed. get the database datatype sorted and then worry about fixing any display problems afterwards.

[cwilkey]

Thanks everyone! I will adhear your advice.

[cwilkey]

Can someone walk me through the ALTER statement to change the datatype from VARCHAR to DATETIME. The only thing I'm worried about is that the dates are stored in the database (mySQL) as mm/dd/yyyy. Can I preserve that format?

[zingmatter]

Try
ALTER TABLE tableName MODIFY fieldName DATETIME NOT NULL

Not sure if this will complain about the missing time that would normally appear in a datetime field. You may have to first update your table to add "00:00:00" to each field. Simple script could do this:

<?php 
$sql = "SELECT * from tableName"
$rs = mysql_query($sql)
while ($row = mysql_fetch_array($rs)) {
    $sql = "UPDATE tableName SET dateField = '".$rs("dateField")." 00:00:00"."' WHERE tableID = ".$rs("tableID").";";
    $dml = mysql_query($sql);
}
?>

(where tableID is you unique/primary key field)

That'll put a fake time into the (varchar defined) field. Then run the ALTER SQL statement.

Hope this helps

[russell_g_1]

creating a temporary column might be a good idea in case you mess it up somehow.

so you could use

alter table tableName add tmpdate datetime

then do

update tableName set tmpdate = convert(oldCol,datetime)

if it works you could then either drop the old column and recreate it or modify it.

if you have direct access to the database there is also no need to write any php. a single update statement will do the same job as zingmatter's code.

[zingmatter]

True. Do you not have phpMyAdmin installed? (http://www.phpmyadmin.net/home_page/index.php)