www.webdeveloper.com
Results 1 to 12 of 12

Thread: Date formatting issue...

  1. #1
    Join Date
    Mar 2004
    Posts
    121

    Date formatting issue...

    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:

    Code:
    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?

  2. #2
    Join Date
    Dec 2005
    Posts
    8
    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'

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    613
    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).
    zing

    | Web Design and Development in Glasgow - 404i |
    | Blog |

    Code:
    -. .-.   .-. .-.   .-. .-.   .-. .-.   .-. .-.
    ||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ 
    |/ \|||x|||/ \|||x|||/ \|||x|||/ \|||x|||/ \|||
    '   '-' '-'   '-' '-'   '-' '-'   '-' '-'   '-'

  4. #4
    Join Date
    Dec 2005
    Posts
    12
    Hey friends i dont know much but can we use the isdate function of vbscript after getting the result from the table

  5. #5
    Join Date
    Oct 2005
    Posts
    59
    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"))

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    613
    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.
    zing

    | Web Design and Development in Glasgow - 404i |
    | Blog |

    Code:
    -. .-.   .-. .-.   .-. .-.   .-. .-.   .-. .-.
    ||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ 
    |/ \|||x|||/ \|||x|||/ \|||x|||/ \|||x|||/ \|||
    '   '-' '-'   '-' '-'   '-' '-'   '-' '-'   '-'

  7. #7
    Join Date
    Dec 2003
    Location
    England, UK
    Posts
    432
    agreed. get the database datatype sorted and then worry about fixing any display problems afterwards.

  8. #8
    Join Date
    Mar 2004
    Posts
    121
    Thanks everyone! I will adhear your advice.

  9. #9
    Join Date
    Mar 2004
    Posts
    121
    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?

  10. #10
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    613
    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 Code:
    <?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
    zing

    | Web Design and Development in Glasgow - 404i |
    | Blog |

    Code:
    -. .-.   .-. .-.   .-. .-.   .-. .-.   .-. .-.
    ||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ 
    |/ \|||x|||/ \|||x|||/ \|||x|||/ \|||x|||/ \|||
    '   '-' '-'   '-' '-'   '-' '-'   '-' '-'   '-'

  11. #11
    Join Date
    Dec 2003
    Location
    England, UK
    Posts
    432
    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.

  12. #12
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    613
    True. Do you not have phpMyAdmin installed? (http://www.phpmyadmin.net/home_page/index.php)
    zing

    | Web Design and Development in Glasgow - 404i |
    | Blog |

    Code:
    -. .-.   .-. .-.   .-. .-.   .-. .-.   .-. .-.
    ||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ /|||x|||\ 
    |/ \|||x|||/ \|||x|||/ \|||x|||/ \|||x|||/ \|||
    '   '-' '-'   '-' '-'   '-' '-'   '-' '-'   '-'

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles