81

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

Beginner
  • 28,539
  • 63
  • 155
  • 235
  • 1
    possible duplicates: [Get row where datetime column = today](http://stackoverflow.com/questions/2583228), [Comparing results with today's date?](http://stackoverflow.com/questions/10395459) – mellamokb Aug 14 '12 at 15:06
  • 1
    Date functions are often database specific. Which database are you using? – Gordon Linoff Aug 14 '12 at 15:06
  • 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.... – marc_s Aug 14 '12 at 15:06
  • 1
    @marc_s and the data type of `Submission_date`. **Edit:** it's in the title but we should confirm it is not of type `Date` :) – D'Arcy Rittich Aug 14 '12 at 15:08
  • 1
    no i get date is not recognised function – Beginner Aug 14 '12 at 15:09
  • 1
    @JIM: there is no `DATE()` built-in function in SQL Server ... he needs to do a `CAST(... AS DATE)` – marc_s Aug 14 '12 at 15:15

7 Answers7

85

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)
davek
  • 22,499
  • 9
  • 75
  • 95
73

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()))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
8

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Allie
  • 1,081
  • 1
  • 13
  • 17
  • sorry, copy/paste error (dificult when all on one line)... change EXTRANET for EXTRANET_users then it should work – Allie Aug 14 '12 at 15:22
  • @marc_s: year etc. were available in SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa258863(v=sql.80).aspx – davek Aug 14 '12 at 15:28
  • @davek: right you are! Hmm.... odd... in the [2012 docs for `YEAR()`](http://msdn.microsoft.com/en-us/library/ms186313.aspx) there's a "other versions" dropdown - SQL Server 2000 isn't listed there - odd ..... – marc_s Aug 14 '12 at 15:30
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)
Santosh Ghimire
  • 69
  • 2
  • 8
2

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

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

Neyomal
  • 1,589
  • 1
  • 12
  • 14
  • 1
    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. – Matthew Hudson Nov 07 '18 at 16:38
0

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.

Andrey Vasilyev
  • 215
  • 2
  • 7
0

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

WHERE  CAST(Submission_date AS DATE) = CAST( getdate() AS DATE)
NiMa Meh
  • 1
  • 2