Results 1 to 6 of 6

Thread: Date format

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Date format

    I am uploading data into a MySql table from a VB application. I want to get today's date using Date. When I look at the results, what is 3/8/2004 in my VB app, is now 2003-08-20. I am guessing this is a formatting problem. Any ideas?
    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you give mysql a date, it must be in year-month-day sequence

    you could upload your dates into a varchar(10) column, alter the table to add a new column with DATE or DATETIME datatype, then update the table and use substring expressions to pull the pieces out of the varchar column to update the date column

    Code:
    update yourtable 
       set datecolumn 
         = concat_ws('/'
               , substring(varcharcolumn 
                       from locate('/',varcharcolumn,4)+1)
               , left(varcharcolumn,locate('/',varcharcolumn,4)-1)
                    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait a sec

    all you want is today's date?

    use the built-in mysql CURRENT_DATE function
    Code:
    insert 
      into yourtable
         ( foo
         , bar
         , datecolumn )
    values
         ( 'qwerty'
         , 'asdfg'
         , current_date )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2003
    Posts
    328
    Thanks for your help. I just changed the type in my SQL table to varchar and it works fine.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, but don't forget to convert it to DATE

    otherwise you will not be able to do any date arithmetic on it

    e.g. where datecolumn between '2004-03-08' and '2004-03-31'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    Good point!!! Thanks.

Posting Permissions

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