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
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!