SQL where datetime column equals today's date?
DatetimeSql Server-2000Datetime Problem Overview
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
Datetime Solutions
Solution 1 - Datetime
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)
Solution 2 - Datetime
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()))
Solution 3 - Datetime
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)
Solution 4 - Datetime
Easy way out is to use a condition like this ( use desired date > GETDATE()-1)
your sql statement "date specific" > GETDATE()-1
Solution 5 - Datetime
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)