Skip to main content

Hello, I have a problem that i am struggling with. I have a table that contains field called [Period] and it stores month dates in following int format: 2201, 2202, 2203 which i need to translate to 2022-01-01, 2022-02-01, 2022-03-01 etc. to date format (always 1st day of month)


The problem is i can convert the source format to date format in the view, but when i am trying to generate a table from that view, i always get an error that [Period] cannot be converted to date format.


I have different options and nothing has worked as timextender always fails to convert to date format. Everything that failed on timextender, worked perfectly on MSSQL so i am not sure what is wrong.


Thanks for your help

Hi


How does the working query you use look in MSSQL?


Usually, it is because you need to apply it differently, compared to MSSQL.

Hey Thomas, basically i can manage to do it like this:

  • CONVERT(date, '20' + [Period] + '01') AS [Date] if the period is in char format.

If it is stored as int, then i convert it to char first

  • CONVERT(date,CONVERT(char(8),'20' + [Period] + '01')) AS [Date]

This actually works when creating the view, but when i try to materialize view into table - it gives me an error

Hey Gediminas,

I gave it a try and this works on my side:

convert(DATE, convert(CHAR(8), 20000000 + [period] * 100 + 1))

 

Hey Frank, your example worked for me as well. Thanks!

Good that it works. I have an additional suggestion, now that I saw the query.

See this image.

As you can see it also does a cast as date despite you already converting it to a date.

So I would not add the convert to date part in the query, as that is being done anyway.
See this image.

 

even better without the second cast, Thomas :-)

Reply


Terms and ConditionsCookie settings