Comparing results with today s date

Question

Is there a way to use the Now   function in SQL to select values with today s date    I was under the impression Now   would contain the time as well as date  but today s date would have the time set to 00 00 00 and therefore this would never match

User · Accepted Answer

There is no native Now   function in SQL Server so you should use   select GETDATE   --2012-05-01 10 14 13 403   you can get day  month and year separately by doing    select DAY getdate     --1 select month getdate     --5 select year getdate    --2012   if you are on sql server 2008  there is the DATE date time which has only the date part  not the time   select cast  GETDATE   as DATE  --2012-05-01

User · Answer

OK  lets do this properly  Select dates matching today  using indexes if available  with all the different date time types present   The principle here is the same in each case  We grab rows where the date column is on or after the most recent midnight  today s date with time 00 00 00   and before the next midnight  tomorrow s date with time 00 00 00  but excluding anything with that exact value    For pure date types  we can do a simple comparison with today s date   To keep things nice and fast  we re explicitly avoiding doing any manipulation on the dates stored in the DB  the LHS of the where clause in all the examples below   This would potentially trigger a full table scan as the date would have to be computed for every comparison   This behaviour appears to vary by DBMS  YMMV    MS SQL Server   SQL Fiddle   db lt  fiddle   First  using DATE  select   from dates  where dte   CAST CURRENT TIMESTAMP AS DATE      Now with DATETIME   select   from datetimes  where dtm  gt   CAST CURRENT TIMESTAMP AS DATE  and dtm  lt  DATEADD DD  1  CAST CURRENT TIMESTAMP AS DATE       Lastly with DATETIME2   select   from datetimes2 where dtm2  gt   CAST CURRENT TIMESTAMP AS DATE  and dtm2  lt  DATEADD DD  1  CAST CURRENT TIMESTAMP AS DATE         MySQL   SQL Fiddle   db lt  fiddle   Using DATE   select   from dates  where dte   cast now   as date      Using DATETIME   select   from datetimes  where dtm  gt   cast  now    as date  and dtm  lt  cast  now     interval 1 day  as date        PostgreSQL   SQL Fiddle   db lt  fiddle   Using DATE   select   from dates  where dte   current date     Using TIMESTAMP WITHOUT TIME ZONE   select   from timestamps where ts  gt    today  and ts  lt   tomorrow        Oracle   SQL Fiddle   Using DATE   select to char dte   YYYY-MM-DD HH24 MI SS   dte from dates  where dte  gt   trunc current date  and dte  lt  trunc current date    1     Using TIMESTAMP   select to char ts   YYYY-MM-DD HH24 MI SS   ts from timestamps where ts  gt   trunc current date  and ts  lt  trunc current date    1       SQLite   SQL Fiddle   Using date strings   select   from dates  where dte    select date  now        Using date and time strings   select dtm from datetimes where dtm  gt   datetime date  now    and dtm  lt  datetime date  now     1 day        Using unix timestamps   select datetime dtm   unixepoch    localtime   from datetimes where dtm  gt   strftime   s   date  now    and dtm  lt  strftime   s   date  now     1 day          Backup of SQL Fiddle code

User · Answer

If you have a table with just a stored date  no time  and want to get those by  now   then you can do this   SELECT   FROM tbl WHERE DATEDIFF d  yourdate  GETDATE    0   This results in rows which day difference is 0  so today

User · Answer

For me the query that is working  if I want to compare with DrawDate for example is  CAST DrawDate AS DATE    CAST  GETDATE   as DATE   This is comparing results with today s date  or the whole query  SELECT TOP  1000    FROM test where DrawName     NULL  and CAST DrawDate AS DATE    CAST  GETDATE   as DATE  order by id desc

User · Answer

This worked for me   SELECT   FROM table where date column date    curdate

User · Answer

You can try   WHERE created date BETWEEN CURRENT TIMESTAMP-180 AND CURRENT TIMESTAMP

User · Answer

You can try this sql code      SELECT  column 1    column 1               FROM  your table       where date format record date    e c Y     date format now      e c Y

User · Answer

Not sure what your asking   However  SELECT  GETDATE     Will get you the current date and time  SELECT  DATEADD dd  0  DATEDIFF dd  0  GETDATE       Will get you just the date with time set to 00 00 00

User · Answer

Building on the previous answers  please note an important point  you also need to manipulate your table column to ensure it does not contain the time fragment of the datetime datatype   Below is a small sample script demonstrating the above   select getdate   --2012-05-01 12 06 51 413 select cast getdate   as date  --2012-05-01  --we re using sysobjects for the example create table test  id int  select   from sysobjects where cast crdate as date    cast getdate   as date  --resultset contains only objects created today drop table test   I hope this helps   EDIT  Following  dwurf comment  thanks  about the effect the above example may have on performance  I would like to suggest the following instead  We create a date range between today at midnight  start of day  and the last millisecond of the day  SQL server count up to  997  that s why I m reducing 3 milliseconds   In this manner we avoid manipulating the left side and avoid the performance impact   select getdate   --2012-05-01 12 06 51 413 select dateadd millisecond  -3  cast cast getdate   1 as date  as datetime   --2012-05-01 23 59 59 997 select cast getdate   as date  --2012-05-01  create table test  id int  select   from sysobjects where crdate between cast getdate   as date  and dateadd millisecond  -3  cast cast getdate   1 as date  as datetime   --resultset contains only objects created today drop table test

User · Answer

Just zero off the time element of the date  e g   SELECT DATEADD dd  DATEDIFF dd  0  getdate     0    I ve used GetDate as that s an MSSQL function  as you ve tagged  but Now   is probably MySQL or you re using the ODBC function call  still should work if you just replace one with the other

User · Answer

Not sure exactly what you re trying to do  but it sounds like GETDATE   is what you re after  GETDATE   returns a datetime  but if you re not interested in the time component then you can cast to a date   SELECT  GETDATE   SELECT  CAST GETDATE   AS DATE

[sql] Comparing results with today's date?

Examples related to sql

Examples related to sql-server

Examples related to current-time