Date format

exdter · #1 (**permalink**) 03-08-04, 13:43

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

r937 · #2 (**permalink**) 03-08-04, 14:09

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)
                )

r937 · #3 (**permalink**) 03-08-04, 14:11

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 )

exdter · #4 (**permalink**) 03-08-04, 14:14

Thanks for your help. I just changed the type in my SQL table to varchar and it works fine.

r937 · #5 (**permalink**) 03-08-04, 14:15

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'

exdter · #6 (**permalink**) 03-08-04, 14:16

Good point!!! Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech