Skip to main content
Question

Converting int/char to date

  • June 8, 2022
  • 6 replies
  • 0 views
G

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

    6 replies

    T
    Forum|alt.badge.img
    • 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

    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

    F
    • fwagner
    • Participating Frequently
    • 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))

    OouWovDxyRKAyoFmYdKUSQ.png

    lpD9OuhAN837v38EI8VBsg.png

     

    G

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

    T
    Forum|alt.badge.img
    • Thomas Lind
    • Community Manager
    • June 9, 2022

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

    See this image.
    yA_oXLyDwfz7UrB1bICBxQ.png

    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.
    unkr5ajY3R9Uo0ai821BnA.png

    F
    • fwagner
    • Participating Frequently
    • June 9, 2022

    even better without the second cast, Thomas :-)

    Terms & ConditionsCookie settingsAccessibility statement