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
Major Red Belt
Major Red 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 thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

All Replies
Major Red Belt
Major Red 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 thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Win free lodging at Domopalooza! We want to hear your Domo story, enter our Dojo contest and win up to four nights lodging at Domopalooza. Click here!