SQL where datetime column equals today's date?

Question

How can I get the records from a db where created date is today's date?

SELECT [Title], [Firstname], [Surname], [Company_name], [Interest] 
FROM [dbo].[EXTRANET_users] 
WHERE DATE(Submission_date) = DATE(NOW())

This doesn't work im using sql server 2000 and submission date is a date time field

possible duplicates: [Get row where datetime column = today](http://stackoverflow.com/questions/2583228), [Comparing results with today's date?](http://stackoverflow.com/questions/10395459) — mellamokb, Aug 14 '12 at 15:06
Date functions are often database specific. Which database are you using? — Gordon Linoff, Aug 14 '12 at 15:06
SQL is the Structured Query Language - it's **not** a database product. We really need to know what **database product** and which version you're using.... — marc_s, Aug 14 '12 at 15:06
@marc_s and the data type of `Submission_date`. **Edit:** it's in the title but we should confirm it is not of type `Date` :) — D'Arcy Rittich, Aug 14 '12 at 15:08
@JIM: there is no `DATE()` built-in function in SQL Server ... he needs to do a `CAST(... AS DATE)` — marc_s, Aug 14 '12 at 15:15

davek · Accepted Answer · 2012-08-14T15:18:20.887

78

Looks like you're using SQL Server, in which case GETDATE() or current_timestamp may help you. But you will have to ensure that the format of the date with which you are comparing the system dates matches (timezone, granularity etc.)

e.g.

where convert(varchar(10), submission_date, 102) 
    = convert(varchar(10), getdate(), 102)

edited Aug 14 '12 at 15:18

answered Aug 14 '12 at 15:09

davek

21,791
7
74
94

got 2 strings that are not equialent: `Jun 7 201` and `2018.06.07` – shmnff Jun 07 '18 at 01:20
please explain what does 102 mean – Ethan Jun 05 '20 at 18:28
it refers to the date and time styles used by CONVERT (https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles) – davek Jun 10 '20 at 10:04
4

Note: To use the yyyy-mm-dd format, use 120 instead of 102. – Exel Gamboa Jun 13 '20 at 11:05

marc_s · Answer 2 · 2012-08-14T15:20:35.847

63

Can you try this?

SELECT [Title], [Firstname], [Surname], [Company_name], [Interest] 
FROM [dbo].[EXTRANET_users] 
WHERE CAST(Submission_date AS DATE) = CAST(GETDATE() AS DATE)

T-SQL doesn't really have the "implied" casting like C# does - you need to explicitly use CAST (or CONVERT).

Also, use GETDATE() or CURRENT_TIMESTAMP to get the "now" date and time.

Update: since you're working against SQL Server 2000 - none of those approaches so far work. Try this instead:

SELECT [Title], [Firstname], [Surname], [Company_name], [Interest] 
FROM [dbo].[EXTRANET_users] 
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, submission_date)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

edited Aug 14 '12 at 15:20

answered Aug 14 '12 at 15:09

marc_s

704,970
168
1,303
1,425

3

He's using SQL Server < 2008, so there is no DATE type. – Jim Aug 14 '12 at 15:14
woops yeh i am connected to 2000 – Beginner Aug 14 '12 at 15:18
The first part worked perfectly for me on SQL Server 2016. Thank you! – metabuddy Nov 01 '18 at 06:53

score 8 · Answer 3 · edited Aug 14 '12 at 15:29

8

There might be another way, but this should work:

SELECT [Title], [Firstname], [Surname], [Company_name], [Interest] 
FROM [dbo].[EXTRANET] 
WHERE day(Submission_date)=day(now) and 
     month(Submission_date)=month(now)
     and year(Submission_date)=year(now)

edited Aug 14 '12 at 15:29

marc_s

704,970
168
1,303
1,425

answered Aug 14 '12 at 15:11

Allie

824
1
9
14

sorry, copy/paste error (dificult when all on one line)... change EXTRANET for EXTRANET_users then it should work – Allie Aug 14 '12 at 15:22
@marc_s: year etc. were available in SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa258863(v=sql.80).aspx – davek Aug 14 '12 at 15:28
@davek: right you are! Hmm.... odd... in the [2012 docs for `YEAR()`](http://msdn.microsoft.com/en-us/library/ms186313.aspx) there's a "other versions" dropdown - SQL Server 2000 isn't listed there - odd ..... – marc_s Aug 14 '12 at 15:30

score 2 · Answer 4 · answered Nov 05 '18 at 06:39

2

Easy way out is to use a condition like this ( use desired date > GETDATE()-1)

your sql statement "date specific" > GETDATE()-1

answered Nov 05 '18 at 06:39

Neyomal

1,459
1
12
14

1

This doesn't answer the question, as this will only work if there are no dates later than the current day, however this is useful to know and has helped me just now. – Matthew Hudson Nov 07 '18 at 16:38

score 2 · Answer 5 · answered Jan 02 '21 at 07:05

2

To get all the records where record created date is today's date Use the code after WHERE clause

WHERE  CAST(Submission_date AS DATE) = CAST( curdate() AS DATE)

answered Jan 02 '21 at 07:05

Santosh Ghimire

49
2
8

SQL where datetime column equals today's date?

5 Answers5

Linked