0

Hi i'm a new coder and messed up on my sql table. Instead of storing my date with a timestamp I made the date in three separate columns: day, month, and year. I now realized that I need these in a timestamp. So I can perform more complicated queries.

Here is what I need the UPDATE to look like:

UPDATE coding_tracker SET coded_at = column(day)"/"column(month_number(month))"/"column(year);

Thank you in advance

Mason Horder
  • 108
  • 11
  • I have been able to make this much so far UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT()); – Mason Horder Nov 24 '18 at 00:14
  • 1
    Is your month column a string or a number? If a string, is it short or long month names? If a number, does it start with 0 or 1 for January? – Nick Nov 24 '18 at 00:44
  • While you're changing this, it's a good time to ask yourself if you are really 100% sure you need a timestamp. Timestamps have a limited range of dates - they cannot represent any year before 1970 nor after 2038. The alternative to timestamp with just a date in it is a "date" (any year from 0000 to 9999 goes) , or a datetime (can also contain the time, not just the date). –  Nov 24 '18 at 00:59
  • my months are names that are capitled ex: November – Mason Horder Nov 24 '18 at 01:01

1 Answers1

1

Assuming your columns are called day, month_number and year, this query should work:

UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month_number, year), '%d/%m/%Y')

In the case where your month column is a name, you can change %m in the above query to %b for short month names (Jan..Dec) or %M for long month names (January..December) e.g. for long names:

UPDATE coding_tracker SET coded_at = STR_TO_DATE(CONCAT_WS('/', day, month, year), '%d/%M/%Y')

Documentation about formats for STR_TO_DATE can be found in the DATE_FORMAT section of the MySQL manual.

Nick
  • 138,499
  • 22
  • 57
  • 95
  • thank you, it works except i need my months to be converted from a string to a number – Mason Horder Nov 24 '18 at 01:02
  • Are your months short or long names? – Nick Nov 24 '18 at 01:03
  • they are full length, and the first letter is capital – Mason Horder Nov 24 '18 at 01:05
  • i am getting this error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 – Mason Horder Nov 24 '18 at 01:12
  • also i have never seen the CONCAT_WS function what is the _WS for – Mason Horder Nov 24 '18 at 01:13
  • @MasonHorder sorry that was a typo, one too many `)`s. I've removed it in my edit. – Nick Nov 24 '18 at 01:15
  • [`CONCAT_WS`](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat-ws) stands for `CONCAT With Separator`, you specify the separator between each value at the beginning. It's shorthand for writing `CONCAT(day, '/', month, '/', year)` – Nick Nov 24 '18 at 01:16