#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0

    Exclamation Casting Varchar to a date


    Hi there,

    I am using MYSQL & PHPAdmin

    I have a column called dispatchdate which is being stores as VARCHAR(255). It is storing dates though as '12/06/2013' format. (DD/MM/YYYY)

    I want to be able to in my PHP page display only the dates for the current month.

    Can somebody please help with the following:

    1) What query can I use in MYSQL to show only results within the current month? (like a GETDATE? function)

    2) Do I need to convert my VARCHAR into DateTime first in my select statement?

    3) How do I go about changing the VARCHAR(255) to DATE? I tried in the STRUCTURE option, (Under CHANGE) but after I changed it to DATE i lost all my values and they displayed NULL


    Can anyone please help with this?

    I am assuming I need to use a CAST function, but i've tried different formats and MYSQL gives me an error.

    Would appreciate any help.
    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,969
    Rep Power
    374
    what I would do first is

    1. create another column called dispatchdate_test (or something) with Date as the type.
    2. do a query such as :

    update table
    set dispatchdate_test = select concat ( substr('get year') "/" substr(get month) "/" sub str "get date")...

    I know my mysql is a bit sketchy but this is one way.

    another way would be to use php to do a select
    1. select primary_key, dispatch_date from.... table
    2. go through the result
    modify the dispatch_date by exploding on "/" and re-arranging it as yyy/mm/dd
    create array[primary_key] and set the modified version to it
    3. do another query (using a foreach loop of the array above) this query will update the table based on primary key and modified date (but use a new column first to make sure dates look the same, when you are happy delete the old column and then rename this new column
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by paulh1983
    update table
    set dispatchdate_test = select concat ( substr('get year') "/" substr(get month) "/" sub str "get date")...
    close, but no cigar

    Code:
    UPDATE daTable
       SET dispatchdate_test = STR_TO_DATE(dispatchdate,'%d/%m/%Y')

    Comments on this post

    • paulh1983 agrees : wow that is why you are the PRO ;)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo