SQL DATE type variable NULL when pushing CSV info to table

Question

I have a CSV file with dates that are in "3/8/2018 9:05:00 AM" format.

When the dates get pushed into a table with column type 'varchar(30)', it becomes "3/8/2018 9:05"

When the dates get pushed into a table with column type 'DATE', it becomes "0000-00-00"

I would like for my columns to be in DATE format so I can sort based on date. Do my CSV dates need to be in 'YYYY-MM-DD' format for this to work?

OR

Is there a way to change the 'Date' format in my SQL table to be in 'mm/dd/yyy hh:mi:ss' or just 'mm/dd/yyyy'?

Right now my only option seems to be to push the dates into SQL with varchar format as is, pull and convert all my dates to "YYYY-MM-DD" format and store them in an array, 'Alter' my column format to "DATE", and then push my array of formatted dates back into the newly altered "DATE" column.

Still new to SQL so any help would be greatly appreciated.

What kind of tool do you use to load data? (SSIS/Import Export Wizard/SQL*Loader/...) — Lukasz Szozda, May 08 '18 at 15:04
@lad2025 I'm not using any specific tool, I'm doing this with PHP code. — Ken, May 08 '18 at 15:19
Possible duplicate of [Importing a CSV to MySQL with different date format](https://stackoverflow.com/questions/8163079/importing-a-csv-to-mysql-with-different-date-format) — apokryfos, May 08 '18 at 16:39

Olá Òké - Ola Oke · Answer 1 · 2018-05-08T16:41:20.680

1

dump your dates/records into a temporary table (staging table)
in a SELECT statement, convert the string dates into actual dates with STR_TO_DATE date function and insert the actual dates into the destination table i.e. SELECT STR_TO_DATE('3/8/2018 9:05:00 AM', '%d/%m/%Y %h:%i:%s %p');

Detail: INSERT INTO destination table (date_col_name) SELECT STR_TO_DATE(date_col_name, '%d/%m/%Y %h:%i:%s %p') FROM temporary_table;

Get more info about date functions here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

edited May 08 '18 at 16:41

answered May 08 '18 at 16:25

Olá Òké - Ola Oke

74
6

Yeah, that's essentially what I had to do in the end. This specific table gets wiped every night and reuploads new data so I just reset and alter the date columns to varchars before uploading new data, format the dates and store them in arrays, alter the table columns to be DATE, and then spit them right back into the table columns. Worked like a charm – Ken May 08 '18 at 17:45

score 0 · Answer 2 · answered May 08 '18 at 15:31

0

Try this:

 select cast('3/8/2018 9:05:00 AM' as date)

answered May 08 '18 at 15:31

Daniel Marcus

2,686
1
7
13

SQL DATE type variable NULL when pushing CSV info to table

2 Answers2