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
This question is related to
datetime
sql-server-2000
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)
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)
Easy way out is to use a condition like this ( use desired date > GETDATE()-1)
your sql statement "date specific" > GETDATE()-1
Source: Stackoverflow.com