20

how to write query to get today's date data in SQL server ?

select * from tbl_name where date = <Todays_date>
DineshDB
  • 5,998
  • 7
  • 33
  • 49
krishna mohan
  • 437
  • 1
  • 4
  • 18

5 Answers5

57

The correct answer will depend on the type of your datecolumn. Assuming it is of type Date:

select * from tbl_name 
where datecolumn = cast(getdate() as Date)

If it is DateTime:

select * from tbl_name 
where cast(datecolumn as Date) = cast(getdate() as Date)

Please note: In SQL Server, a datediff() (or other calculation) on a column is NOT Sargable, whereas as CAST(column AS Date) is.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
6

Seems Mitch Wheat's answer isn't sargable, although I am not sure this is true.

Please note: a DATEDIFF() (or other calculation) on LHS is NOT Sargable, whereas as Cast(x to Date) is.

SELECT * 
FROM tbl_name 
WHERE date >= cast(getdate() as date)
and date < cast(getdate()+1 as date)
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • actually, later versions of sql server (2K8)+ appear to be 'cast-to-date' s-argable aware. I use to think this would preclude the use of an index and then I tested it. – Mitch Wheat Mar 05 '15 at 08:34
  • @MitchWheat I am glad to hear that. I had my doubts and started browsing. As you can tell from my link it was written last year. The author doesn't mention which version he is using though – t-clausen.dk Mar 05 '15 at 08:36
  • 1
    Please note: a datediff() (or other calculation) on LHS is NOT Sargable, whereas as Cast(x to Date) is. – Mitch Wheat Mar 05 '15 at 09:38
4
select * from tbl_name where date = cast(getdate() as Date)

for CAST see http://msdn.microsoft.com/en-us/library/ms187928.aspx and for GETDATE() see https://msdn.microsoft.com/en-IN/library/ms188383.aspx

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
-1

It works for me. Please check it out.

SELECT * FROM tbl_name WHERE created_at = cast(Date(Now()) as Date);
Najathi
  • 2,529
  • 24
  • 23
-3
select * from tbl_name where date(column_name) = CURDATE();

Now you can get today inserted rows

Irshad muhammed
  • 1
  • 2
    Hi Irshad. The question is asking for a solution in SQL Server. The SQL statement you posted won't run on SQL Server. – TT. Aug 21 '20 at 09:47