SQL query to find the previous date, current date data

Question

Post reply

SQL query to find the previous date, current date data

ShawnBryan

SSCrazy

Points: 2157
More actions
October 23, 2016 at 9:40 am

#400922

If am running today query ( 10/23/2016), then I need get data from following dates 10/22/2016,10/23/2016.can anyone tell me an idea for how to do it?
my query is
SELECT TOP 1000 [action_id]
,DBO.UtcToLocal(creation_date) as Creation_Date
,[event_name]
,[person_num]
FROM [bcore].[dbo].[TEST] WHERE person_num = '00010167'
expected query output:
1684862016-10-22 15:41:22.823CLOCK_IN00010167
1687952016-10-22 19:47:22.823CLOCK_OUT00010167
1688862016-10-23 10:39:22.823CLOCK_IN00010167
1689182016-10-23 12:25:22.823CLOCK_OUT00010167

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Something like this:

SELECT *

FROM

(SELECT 1 as PersonID, '2016-10-21 11:00' as evntTime, 'CLOCK_IN' as evntType

UNION ALL

SELECT 1, '2016-10-21 20:45', 'CLOCK_OUT'

UNION ALL

SELECT 1, '2016-10-22 3:45', 'CLOCK_IN'

UNION ALL

SELECT 1, '2016-10-22 12:45', 'CLOCK_OUT'

UNION ALL

SELECT 2, '2016-10-23 11:00', 'CLOCK_IN') x

WHERE x.evntTime > GETDATE()-1;

You may need to tweak the WHERE clause so it returns exactly what you want.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

pietlinden (10/23/2016)
You may need to tweak the WHERE clause so it returns exactly what you want.

Your answer is perfect if you need to filter for the past 24 hours. As for the original post it looks like the result must match today and yesterday (whole day), so you'll need to filter only on the DATE part. That would lead to altering the WHERE clause to:

...

WHERE x.evntTime > cast(GETDATE()-1 as date)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

HanShi (10/24/2016)
...
WHERE x.evntTime > cast(GETDATE()-1 as date)

since evntTime seems to be DATETIME datatype, wouldn't that leave an implicit conversion in the execution to compare it to the calculated DATE value? If you want to avoid the conversion, maybe something like:

WHERE x.evntTime > DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

Chris Harshman (10/25/2016)
HanShi (10/24/2016)
...
WHERE x.evntTime > cast(GETDATE()-1 as date)
since evntTime seems to be DATETIME datatype, wouldn't that leave an implicit conversion in the execution to compare it to the calculated DATE value? If you want to avoid the conversion, maybe something like:
WHERE x.evntTime > DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)

Your absolutely right 😎

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ShawnBryan SSCrazy Points: 2157 More actions · Answer 5

Hi,

I need to display tomorrow date result also . could you guys guide me achieve the same .

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

philand3 (10/31/2016)
Hi,
I need to display tomorrow date result also . could you guys guide me achieve the same .

Here's a post with some common date routines. It should be a big help. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions