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

308675 次浏览
小开

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()))
小开
最佳答案

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)
小开

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)
小开

Not sure if this will work for older versions of SQL, but...

I figured out that the following works great if you want to be precise about returning results in a "calendar day" fashion for any "number of days ago" (probably works for months and years too), regardless of what time, year, month, or day of the month you are running your query on while being exact about what is and isn't included in your results...

For example, let's say you want to return "Total Logins in Past 7 Days" but today is the 4th day of the month, or you want the "past 7 calendar days" without including some chunk of today, day 8, or cutting day 7 in half. When using day(), dateadd(), and/or getdate() in various combinations, you may encounter issues depending on what time/day/month/year you run your query as this will typically affect your results if using =, >, <, >=, <=, in operators...

So I found the following is one way to get around that:

"Yesterday's Logins"

select count(Id) as 'Logged in Yesterday'
from dbo.Users
where LastLoggedIn between dateadd(day, -1, convert(date, getdate()))
and dateadd(day, -0, convert(date, getdate()))

"Logins for Past 7 Days" (without including some chunk of today or day 8)

select count(Id) as 'Logins for Past 7 Days'
from dbo.Users
where LastLoggedIn between dateadd(day, -7, convert(date, getdate()))
and dateadd(day, -0, convert(date, getdate()))

and so on...

You can pretty much adjust the two values being subtracted for anything you want and it will always give you a calendar-day level of precision.

Please let me know if I can improve this answer as I just fiddled with it until I got the results I wanted, so this may or may not be the best way to go about it.

小开

It seems to me that this where clause would be useful:

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

提交答案