Change the Date format in DB tool

SriHari52

Hi everyone!

I'm trying to figure out how Alteryx DB tools could convert from date Format  into the corresponding Date , but I haven't got any success. 

For example: Convert the value "201805.00000" into this value "201805" (YYYYMM)

Can you any help on that 

CodeMonkey

Hi!

For the example you gave, it should only be a few quick data type changes in the SQL.

Let's say that your column containing the value "201805.00000" is called COLUMN1.

Assuming MS SQL server 2008+ syntax:

You could change the data type to integer (removing the decimal places) with CAST(COLUMN1 AS int). This would leave you with a  6-digit YYYYMM number.

You could then output a string if you wanted with  CAST(CAST(COLUMN1 AS int) AS varchar). This would leave you with a "YYYYMM" string.

If you wanted to get an actual date out of it, you could use:

CONVERT(datetime, CAST(CAST(COLUMN1 AS int)  AS varchar)+'01', 112)

This uses the CONVERT function on the "YYYYMM" string, with the 112 code specifying the format your data is in.

More details below if you're curious about convert:

https://www.w3schools.com/sql/func_sqlserver_convert.asp

These same approaches will work even if you're using a different flavor of SQL, you may just need to use SQL functions with slightly different names!