CONVERT TO INT DATABASE YEAR MONTH HOUR MINUTE MSSQL-CodePudding

Good day!

I would like to convert from a column in sql but i'm having an error. How to convert this properly?

Tried:

(CONVERT([int],(((right(datepart(year, [dbo.QI_Data.DateEntered]),(2))*(100000000)
                        datepart(month, [dbo.QI_Data.DateEntered])*(1000000))
                        datepart(day, [dbo.QI_Data.DateEntered])*(10000))
                        datepart(hour, [dbo.QI_Data.DateEntered])*(100))
                        datepart(minute, [dbo.QI_Data.DateEntered]))) AS DateEnteredToInt

I'm getting the column from another table called QI_Data and column DateEntered. I'm creating view

Also I'm getting Arithmetic overflow error expression

CodePudding user response：

Since 22*100000000 is more than a MAX_INT, you need to use a BIGINT

SELECT (((CONVERT(BIGINT,right(datepart(year, QI_Data.DateEntered),(2)))*(100000000)
                        CONVERT(BIGINT,datepart(month, QI_Data.DateEntered))*(1000000))
                        CONVERT(BIGINT,datepart(day, QI_Data.DateEntered))*(10000))
                        CONVERT(BIGINT,datepart(hour, QI_Data.DateEntered))*(100))
                        CONVERT(BIGINT,datepart(minute, QI_Data.DateEntered)) AS DateEnteredToInt
FROM (VALUES (GETDATE()))QI_Data(DateEntered)

This would do the job to :

SELECT
    CONVERT(BIGINT,CONCAT(right(datepart(year, QI_Data.DateEntered),(2)),
        datepart(month, QI_Data.DateEntered),
        datepart(day, QI_Data.DateEntered),
        datepart(hour, QI_Data.DateEntered),
        datepart(minute, QI_Data.DateEntered)
    ))
FROM (VALUES (GETDATE()))QI_Data(DateEntered)