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

Question

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

Which dbms are you using? (When it comes to date/time, many products are far from ANSI SQL compliant.) — jarlh, May 20 '22 at 11:13

score 1 · Answer 1 · answered May 20 '22 at 10:53

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

Thank you @CF5 ! Maybe now, I will start to learn SQL language ;) It is very clear! — Vero, May 20 '22 at 11:34

Filburt · Answer 2 · 2022-05-20T11:52:03.370

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

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

2 Answers2