Skip to main content
To homepage To homepage
Solved

Converting int/char to date

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.

 

View original
    Did this topic help you find an answer to your question?

    6 replies

    Thomas Lind
    Community Manager
    Forum|alt.badge.img+5
    • Thomas Lind
    • Community Manager
    • 1028 replies
    • 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
    • 33 replies
    • 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
    • 1028 replies
    • 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
    • 33 replies
    • June 9, 2022

    even better without the second cast, Thomas :-)

    Reply


    Most helpful members this week

    Terms and ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings