SQL where datetime column equals today's date?

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

5 Answers

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) 

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())) 

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) 

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

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

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)