To homepage To homepage
Solved

Converting int/char to date

G
Rank

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

icon

Best answer by Thomas Lind 9 June 2022, 09:57

View original

6 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi

How does the working query you use look in MSSQL?

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

G
Rank

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

fwagner
Rank
Userlevel 2
Badge +3

Hey Gediminas,

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

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

 

G
Rank

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

Thomas Lind
Rank
Userlevel 6
Badge +5

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.

 

fwagner
Rank
Userlevel 2
Badge +3

even better without the second cast, Thomas :-)

Reply


Terms and ConditionsCookie settings