Skip to main content
To homepage To homepage
Solved

Converting int/char to date

  • June 8, 2022
  • 6 replies
  • 263 views
G
Contributor

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

Best answer by Thomas Lind

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.

 

    6 replies

    Thomas Lind
    Community Manager
    Forum|alt.badge.img+5
    • Thomas Lind
    • Community Manager
    • June 8, 2022

    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
    Contributor

    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
    Employee
    Forum|alt.badge.img+4
    • fwagner
    • Employee
    • June 8, 2022

    Hey Gediminas,

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

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

     

    G
    Contributor

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

    Thomas Lind
    Community Manager
    Forum|alt.badge.img+5
    • Thomas Lind
    • Community Manager
    • Answer
    • June 9, 2022

    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
    Employee
    Forum|alt.badge.img+4
    • fwagner
    • Employee
    • June 9, 2022

    even better without the second cast, Thomas :-)

    Terms and ConditionsCookie settingsAccessibility statement