• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Yesterday when column is date/time

I am trying to pick up calendar entries for yesterday, I am currently using

WHERE  dbo.CalendarEvents.StartDateTime = DATEADD(DAY, - 1, DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)))

But it is not coming up, do I need to use a between to try and catch it
0
rocky050371
Asked:
rocky050371
1 Solution
 
Habib PourfardCommented:
You could write:
WHERE CONVERT(VARCHAR(10), dbo.CalendarEvents.StartDateTime, 111) = CONVERT(VARCHAR(10), DATEADD(d, -1, GETDATE()), 111)

Open in new window

0
 
tim_csCommented:
Something like this should work.

WHERE
   dbo.CalendarEvents >= DATEADD(dd,0,DATEDIFF(dd,1,Current_TIMESTAMP)) AND dbo.CalendarEvents < DATEADD(dd,0,DATEDIFF(dd,0,Current_TIMESTAMP))
0
 
tim_csCommented:
pourfard's will work but if you have an index on CalendarEvents it will not be used.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
jhheiderCommented:
Looks like:

WHERE  DATEPART(dbo.CalendarEvents.StartDateTime) = DATEPART(DATEADD(DAY, - 1, GETDATE()))

based on translating my MySQL into MSSQL.
0
 
exodusterCommented:
I would rather use that:
WHERE DATEPART(YEAR, dbo.CalendarEvents.StartDateTime) = DATEPART(YEAR, GETDATE()-1) AND DATEPART(MONTH, dbo.CalendarEvents.StartDateTime) = DATEPART(MONTH, GETDATE()-1) AND DATEPART(DAY, dbo.CalendarEvents.StartDateTime) = DATEPART(DAY, GETDATE()-1)
0
 
tim_csCommented:
Small update on mine as I missed the StartDateTime column.  And as with pourfard's example exoduster's will not make use of an index on StartDateTime if one exists or if you add one later.  

WHERE
   dbo.CalendarEvents.StartDateTime >= DATEADD(dd,0,DATEDIFF(dd,1,Current_TIMESTAMP)) AND dbo.CalendarEvents.StartDateTime < DATEADD(dd,0,DATEDIFF(dd,0,Current_TIMESTAMP))
0
 
hnasrCommented:
Try:

WHERE  dbo.CalendarEvents.StartDateTime BETWEEN DATEADD("m",-1, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now