show data for today only

Question

Post reply

show data for today only

jamesstirling01

Mr or Mrs. 500

Points: 596

September 6, 2021 at 8:59 am

#3927121

Hi,

I have a table call data setup like the below:

ID (PK) Initial (nchar10) RFID (nchar10) Time (datetime) Type (Int)

I have a current query that displays the following

Clockin-Clockout

;with ClockIn as
(select [Initial],[RFID],[Time] as ClockInTime from Data where Type=1)
,ClockOut as
(select [Initial],[RFID],[Time] as ClockOutTime from Data where Type=2)
select * from (
select
ClockIn.[Initial]
,ClockIn.[RFID]
,ClockIn.[ClockInTime]
,ClockOut.[ClockOutTime]
,ROW_NUMBER() over (
partition by ClockIn.[Initial],ClockIn.[RFID],ClockIn.[ClockInTime]
order by ClockIn.[ClockInTime] asc) as rownum
from ClockIn
inner join ClockOut
on ClockOut.[Initial]=ClockIn.[Initial]
and ClockOut.[RFID]=ClockIn.[RFID]
and ClockOut.[ClockOutTime] > ClockIn.[ClockInTime]
) ClockCombined where rownum=1

Basically I would only want to show todays data only and non of the historical data can anyone assist with the date side of the query.

Thanks James

Viewing 11 posts - 1 through 10 (of 10 total)

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

DesNorton SSC-Insane Points: 24276 More actions · Answer 1

With no readily available test data, I would try to edit the 1st CTE as follows

;with ClockIn as
(select [Initial],[RFID],[Time] as ClockInTime from Data where Type=1 and [Time] >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) )

How to post data/code on a forum to get the best help.

jamesstirling01 Mr or Mrs. 500 Points: 596 More actions · Answer 2

Hi DesNorton,

I have edited the query to as you advise but it returns no results.

My data is generated from a VB.net Windows application, i would like to get this resolved and will assist were required as I require this query to complete the project.

Kind regards,

James

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

You have no data for today in the screenshot you have provided.

The script Des gave will give you data for today only, since you have none you get a empty result set.

Dates in SQL are YMD if you are doing date manipulation in your VB.NET application then you need to pass the string to SQL in a valid ISO format.

Based on your screen shot you have data for 9th March 2021 and 9th June 2021, there is no data in that table for 6th September 2021 or 3rd September 2021

jamesstirling01 Mr or Mrs. 500 Points: 596 More actions · Answer 4

Hi Ant,

You are incorrect the date data in the image is:

3rd of September 2021 & 6th of the September 2021

I agree this will no doubt have to changed to be the correct format but i am new to SQL and learning.

Kind regards

James

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

Thats a negative I'm afraid, those dates are March and June, the way SQL formats dates in the output is always YMD, not YDM as you can see from this test case.

No matter the input format, when you select the date in SQL it is YMD

Your dates are for 9th March and 9th June according to what SQL thinks you have.

declare @datetable table (fmt char(3), somedate datetime)
set dateformat dmy
insert into @datetable values ('dmy','31-01-2021')
set dateformat mdy
insert into @datetable values ('mdy','02-28-2021')
set dateformat ymd
insert into @datetable values ('ymd','2021-03-09')

select * from @datetable

date_formats

Ant-Green SSC Guru Points: 113445 More actions · Answer 6

declare @datetable2 table (somedate datetime)
insert into @datetable2 values ('2021-03-09'),('2021-06-09')
select datename(month,somedate), somedate from @datetable2

date_formats

jamesstirling01 Mr or Mrs. 500 Points: 596 More actions · Answer 7

Ok i see what you are saying so how do i change the format so my SQL date is 2021-09-06 instead of 2021-06-09 as my language is UK?

Ant-Green SSC Guru Points: 113445 More actions · Answer 8

That would be up to the application which is inserting the data, it is sending the wrong date to SQL to store so it needs to be tackled at the application layer unfortunately.

jamesstirling01 Mr or Mrs. 500 Points: 596 More actions · Answer 9

I have altered the application to now record the correct date function.

But i am still struggling to get it to show todays data only

Clockin-Clockout

Ant-Green SSC Guru Points: 113445 More actions · Answer 10

DECLARE @CardSwipes TABLE (Initials VARCHAR(5), RFID INT, SwipeTime DATETIME, Flag TINYINT)

INSERT INTO @CardSwipes VALUES
('GA',4321,'2021-09-01 09:00',1),
('AG',1234,'2021-09-07 08:00',1),
('AG',1234,'2021-09-07 09:00',2),
('AG',1234,'2021-09-07 10:00',1),
('JS',5678,'2021-09-07 20:00',1)

;WITH CardSwipeCTE AS (
SELECT ROW_NUMBER() OVER (PARTITION BY RFID ORDER BY SwipeTime DESC) AS RN,
Initials, RFID, SwipeTime, Flag
FROM @CardSwipes)
SELECT * FROM CardSwipeCTE WHERE RN = 1 AND Flag = 1
and swipetime >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

This will get you what you want based on the sample date I did on the other topic.

Can you please read through https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help also on how to post code as its beneficial to have it as TSQL rather than an image sometimes especially if we need to manipulate the data, people wont spend time typing it all out