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

Translucid

possible duplicates: Get row where datetime column = today, Comparing results with today's date? – Tropism

Date functions are often database specific. Which database are you using? – Shaff

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.... – Maxa

@Maxa and the data type of Submission_date. Edit: it's in the title but we should confirm it is not of type Date :) – Evilminded

no i get date is not recognised function – Translucid

@JIM: there is no DATE() built-in function in SQL Server ... he needs to do a CAST(... AS DATE) – Maxa

Answer 1

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)

Biophysics

got 2 strings that are not equialent: Jun 7 201 and 2018.06.07 – Carioca

please explain what does 102 mean – Castellano

it refers to the date and time styles used by CONVERT (learn.microsoft.com/en-us/sql/t-sql/functions/…) – Biophysics

Note: To use the yyyy-mm-dd format, use 120 instead of 102. – Skit

Answer 2

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

Maxa

He's using SQL Server < 2008, so there is no DATE type. – Neurath

woops yeh i am connected to 2000 – Translucid

The first part worked perfectly for me on SQL Server 2016. Thank you! – Riba

Answer 3

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)

Liguria

sorry, copy/paste error (dificult when all on one line)... change EXTRANET for EXTRANET_users then it should work – Liguria

@marc_s: year etc. were available in SQL Server 2000: msdn.microsoft.com/en-us/library/aa258863(v=sql.80).aspx – Biophysics

@davek: right you are! Hmm.... odd... in the 2012 docs for YEAR() there's a "other versions" dropdown - SQL Server 2000 isn't listed there - odd ..... – Maxa

Answer 4

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)

Mccracken

I like the concept; but some servers require GETDATE() instead. – Palumbo

Answer 5

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

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

Slalom

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. – Davao

Answer 6

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.

Dieter

Answer 7

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

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

Huggins