How do I use select with date condition

Question

In sqlserver  how do I compare dates  For example   Select   from Users where RegistrationDate     1 20 2009    RegistrationDate is datetime type   Thanks

User · Answer

if you do not want to be bothered by the date format, you could compare the column with the general date format, for example

select * From table where cast (RegistrationDate as date) between '20161201' and '20161220'

make sure the date is in DATE format, otherwise cast (col as DATE)

User · Answer

Another feature is between   Select   from table where date between  2009 01 30  and  2009 03 30

User · Answer

Select   from Users where RegistrationDate  gt   CONVERT datetime   01 20 2009   103    is safe to use  independent of the date settings on the server   The full list of styles can be found here

User · Answer

If you put in   SELECT   FROM Users WHERE RegistrationDate  gt    1 20 2009     it will automatically convert the string  1 20 2009  into the DateTime format for a date of 1 20 2009 00 00 00  So by using  gt   you should get every user whose registration date is 1 20 2009 or more recent   Edit  I put this in the comment section but I should probably link it here as well  This is an article detailing some more in depth ways of working with DateTime s in you queries  http   www databasejournal com features mssql article php 2209321 Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges htm

User · Answer

I always get the filter date into a datetime  with no time  time  00 00 00 000   DECLARE  FilterDate  datetime --final destination  will not have any time on it DECLARE  GivenDateD  datetime --if you re given a datetime DECLARE  GivenDateS  char 23  --if you re given a string  it can be any valid date format  not just the yyyy mm dd hh mm ss mmm that I m using  SET  GivenDateD  2009 03 30 13 42 50 123  SET  GivenDateS  2009 03 30 13 42 50 123   --remove the time and assign it to the datetime  FilterDate dateadd dd  datediff dd  0   FilterDateD   0  --OR  FilterDate dateadd dd  datediff dd  0   FilterDateS   0    You can use this WHERE clause to then filter   WHERE ColumnDateTime gt   FilterDate AND ColumnDateTime lt  FilterDate 1   this will give all matches that are on or after the beginning of the day on 2009 03 30 up to and including the complete day on 2009 03 30  you can do the same for START and END filter parameters as well   Always make the start date a datetime and use zero time on the day you want  and make the condition        Always make the end date the zero time on the day after you want and use   lt     Doing that  you will always include any dates properly  regardless of the time portion of the date

User · Answer

select sysdate from dual 30-MAR-17  select count 1  from masterdata where to date inactive from date  DD-MON-YY  between  01-JAN-16  to  31-DEC-16   12998 rows

[sql] How do I use select with date condition?

Examples related to sql

Examples related to sql-server