sql where date is bigger than integer date

Question

I have a stored procedure and table consists of integer for date for year, month, day.

BEGIN
    SET NOCOUNT ON;

    SELECT
        [Address],
    FROM [dbo].[Stats]
    WHERE DATEADD(year, [year]-1900, DATEADD(month, [month]-1, DATEADD(day, [day]-1, DATEADD(hour, [hour]-1, DATEADD(minute, [minute]-1, 0))))) >= @datetime
END

If I pass datetime into stored procedure how can I do where statement. How can I simplifiy procedure below?

Also I tried this

DATEADD(year, [year]-1900, DATEADD(month, [month]-1, DATEADD(day, [day]-1, DATEADD(hour, [hour]-1, DATEADD(minute, [minute]-1, 0))))) as [DateTime]
FROM [dbo].[Stats]
AND [DateTime] >= @datetime

And it says [DateTime] doesn't exist, how can I assign AS new field from calculation in stored procedure? It works normal in query.

Tag your question with the database you are using (which is pretty obviously SQL Server). — Gordon Linoff, Jun 05 '18 at 14:07
Possible duplicate of [How to create a Date in SQL Server given the Day, Month and Year as Integers](https://stackoverflow.com/questions/35576983/how-to-create-a-date-in-sql-server-given-the-day-month-and-year-as-integers) — CodeCaster, Jun 05 '18 at 14:07
You cannot use an alias like `[DateTime]` in a where clause, you need to repeat the entire calculation again in your `AND` statement. I dont believe that is works like that in `normal query` — GuidoG, Jun 05 '18 at 14:20

score 1 · Answer 1 · edited Jun 05 '18 at 14:46

1

Use datetimefromparts():

WHERE datetimefromparts([year], [month], [year], [hour], [minute], 0)  >= @datetime

I'm not sure what the arithmetic you are doing for your conversion is for. Obviously, you can include such expressions as arguments to the function.

edited Jun 05 '18 at 14:46

EzLo

13,780
10
33
38

answered Jun 05 '18 at 14:06

Gordon Linoff

1,242,037
58
646
786

datefromparts accept only 3 arguments – sensei Jun 05 '18 at 14:09
So where (datefromparts([year], [month], [year]) >= @datetime and datefromparts([hour], [minute], 0) >= @datetime) ? – A. van Esveld Jun 05 '18 at 14:13
If you just have until day (no time), then use `datefromparts()`, if you want full datetime then `datetimefromparts()`. – EzLo Jun 05 '18 at 14:45

score 0 · Answer 2 · answered Jun 05 '18 at 14:29

SELECT DQ.Address, DQ.[DateTime]
      (SELECT *,
          DATEADD(year, [year]-1900, DATEADD(month, [month]-1, DATEADD(day, [day]-1, DATEADD(hour, [hour]-1, DATEADD(minute, [minute]-1, 0))))) 
             AS [DateTime] 
      )
           FROM [dbo].[Stats]) AS DQ
WHERE  DQ.[DateTime] >= @datetime

sql where date is bigger than integer date

2 Answers2