ETL: SQL Set column type

Reply
Highlighted
Visitor

ETL: SQL Set column type

Is there a way to set the column type of while you are creating the column in mysql? I have a column that I am creating based on a date (Min Cust Svc Start) and a comparison to another date (1/1/2015).  I want "Min Cust Svc Start 15" to be a datetype column; however, it is defaulting to text.  I know I can change it in alter tab but am wondering if there is a way to change it in the query below (while I am creating the field)?  Also, am looking for guidance on which is the preferred method...

 

select tc.*,
CASE
WHEN `Min Cust Svc Start` < '2015-01-01' THEN
'2015-01-01'
ELSE `Min Cust Svc Start`
END AS `Min Cust Svc Start 15`
from `transform_cust_svc` tc


Accepted Solutions
AS Black Belt
Black Belt

Re: ETL: SQL Set column type

Definitely:

CAST(value AS type)

 

For you that would be like 

SELECT

...

CAST(`Min Cust Svc Start` as Date)

...

 

Depending on what your string data looks like you might have to do some formatting, but the CAST could be the one you want.

Also str_to_date works great.  Format as necessary.

STR_TO_DATE(tc.`Min Cust Svc Start, '%d/%m/%Y')

 

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

All Replies
AS Black Belt
Black Belt

Re: ETL: SQL Set column type

Definitely:

CAST(value AS type)

 

For you that would be like 

SELECT

...

CAST(`Min Cust Svc Start` as Date)

...

 

Depending on what your string data looks like you might have to do some formatting, but the CAST could be the one you want.

Also str_to_date works great.  Format as necessary.

STR_TO_DATE(tc.`Min Cust Svc Start, '%d/%m/%Y')

 

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Looking for the latest Dojo Community solutions? Click on the "NEW SOLUTIONS' widget title or go to https://dojo.domo.com/solutions Click here!