SQL Convert data time format (yyyy-mm-dd 00:00:00.000) to yyyymmdd as int-CodePudding

It must be very simple, but I don't know SQL language very well. I need to filter data by date which is in this format:

How to do it right to filter data this way?

FROM [TableName] where
FileDate>=20220505

I've already tried the command LEFT and CAST but with no success

CodePudding user response：

Something like this may work:

declare  @now Datetime = getdate();
declare  @intNow int = cast(cast(datepart(year, @now) as varchar(4))   RIGHT('00' CAST(datepart(month, @now) AS VARCHAR(2)),2)   RIGHT('00' CAST(datepart(day, @now) AS VARCHAR(2)),2) as int)

Although if you have your date to check against in the right format e.g. using:

declare @dateToCheck Datetime = cast(cast(20220505 as varchar) as datetime)

And then

FileDate>= @dateToCheck

it should work

CodePudding user response：

You can create an integer representation of your datetime by multiplying and adding the date parts:

year  * 10000    20220000
month * 100           500
day                     5
-------------------------
                 20220505

...
FROM [TableName]
WHERE (DATEPART(year, [FileDate]) * 10000)   (DATEPART(month, [FileDate]) * 100)   (DATEPART(day, [FileDate])) >= 20220505

However I'd still look into fixing the condition input format instead.

Credit to @Rangani in Yesterday's date in SSIS package setting in variable through expression for "multiply and add instead of string concat" trick