May 28, 2012 at 5:06 am
I have a load of records in a table. Each with a duedate. The duedate is a datetime field.
I want to get todays records. Past the current date time. So if I have a record which is due at 12:00 on 28/5/2012, then I dont want to show it in my filter because the time has passed, but if the time was 13:00 on today I would like to show it. Alternatively how would the logic work for tomorrow? And the current week?
ID subject DueDate
1 Task 2012-05-28 12:00:00.000
2 Phone call 2012-05-28 13:00:00.000
3 email 2012-05-28 12:30:00.000
4 Task 2012-05-28 14:00:00.000
5 Phone call 2012-05-28 11:00:00.000
May 28, 2012 at 5:37 am
If you just want to show records that are due use GETDATE()
SELECT *
FROM YourTable
WHERE
DueDate > GETDATE()
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 28, 2012 at 7:10 am
This returns everything for that day. But I only want to return everything for the day after the current time.
May 28, 2012 at 7:19 am
Sachin 80451 (5/28/2012)
I have a load of records in a table. Each with a duedate. The duedate is a datetime field.I want to get todays records. Past the current date time. So if I have a record which is due at 12:00 on 28/5/2012, then I dont want to show it in my filter because the time has passed, but if the time was 13:00 on today I would like to show it. Alternatively how would the logic work for tomorrow? And the current week?
ID subject DueDate
1 Task 2012-05-28 12:00:00.000
2 Phone call 2012-05-28 13:00:00.000
3 email 2012-05-28 12:30:00.000
4 Task 2012-05-28 14:00:00.000
5 Phone call 2012-05-28 11:00:00.000
so if time is now 12:31, you want to see ID 2 and 4?
SELECT
*
FROM
YourTable
WHERE
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,1,GETDATE()))
May 28, 2012 at 7:27 am
This also returns records with the following due date 29/5/2012 00:00:000?
May 28, 2012 at 7:34 am
so if time is now 12:31, you want to see ID 2 and 4?
So based on your data above what would you like to be returned as Anthony asked?
Are you only after records returned after the current date time for that day?
SELECT
*
FROM
YourTable
WHERE
DueDate > GETDATE() AND DAY(DueDate) = Day(GETDATE())
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 28, 2012 at 7:38 am
Sachin 80451 (5/28/2012)
This also returns records with the following due date 29/5/2012 00:00:000?
Can you post us some more sample data with varing dates, then give us a date and time and then tell is which ID's should be returned.
I've just run this, and it didnt return the row with ID 6, did you put <= CONVERT or < CONVERT
declare @tab table (id int, subject nvarchar(10), duedate datetime)
insert into @tab values
(1,'Task','2012-05-28 12:00:00.000'),
(2,'Phone call','2012-05-28 13:00:00.000'),
(3,'email','2012-05-28 12:30:00.000'),
(4,'Task','2012-05-28 14:00:00.000'),
(5,'Phone call','2012-05-28 11:00:00.000'),
(6,'Test','2012-05-29 00:00:00.000')
select * from @tab
SELECT
*
FROM
WHERE
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,1,GETDATE()))
May 28, 2012 at 7:40 am
Yes fantastic that works. I did only want records for that day and after current time.
what would the logic be for tomorrow?
or this week?
May 28, 2012 at 7:47 am
Sachin 80451 (5/28/2012)
Yes fantastic that works. I did only want records for that day and after current time.what would the logic be for tomorrow?
or this week?
For tomorrow
To see from current time till 29-05-2012 23:59:59.999 or 29-05-2012 00:00:00.000 to 29-05-2012 23:59:59.999
If from current time to midnight
WHERE
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,2,GETDATE()))
If midnight to midnight
WHERE
DueDate > CONVERT(DATE,DATEADD(DAY,1,GETDATE())) AND DueDate < CONVERT(DATE,DATEADD(DAY,2,GETDATE()))
For week
Change DATEADD(DAY,1,GETDATE())) to DATEADD(DAY,7,GETDATE()))
Just a case of getting the right DATE functions to create the right date range
May 28, 2012 at 7:48 am
You can use these in your WHERE clause to change what is returned:
SELECT
DAY(DATEADD(dd,1,GETDATE())) --Tomorrow
SELECT
CAST(DATEADD(DAY , 7-DATEPART(WEEKDAY,GETDATE()),GETDATE()) AS DATE) --Last Day of the Week
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 28, 2012 at 8:08 am
For tomorrow
To see from current time till 29-05-2012 23:59:59.999 or 29-05-2012 00:00:00.000 to 29-05-2012 23:59:59.999
If from current time to midnight
WHERE
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,2,GETDATE()))
// Yes I require current time to midnight for today (I have to change the 'DATE' part to 'DATETIME' above and returns records until the 30/5/2012 till midnight
If midnight to midnight
WHERE
DueDate > CONVERT(DATE,DATEADD(DAY,1,GETDATE())) AND DueDate < CONVERT(DATE,DATEADD(DAY,2,GETDATE()))
// This returns one record for the '2012-05-30 00:00:00.000' even though I have data for 28 and 29 May.
For week
Change DATEADD(DAY,1,GETDATE())) to DATEADD(DAY,7,GETDATE()))
Just a case of getting the right DATE functions to create the right date range
// will this return the current 7 days (This week) or the next 7 days from the current date time?
May 28, 2012 at 8:11 am
// will this return the current 7 days (This week) or the next 7 days from the current date time?
The next 7 days from the current date, the SQL I posted returns the end of the current week
Which one you use depends on what you are looking for
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 28, 2012 at 8:15 am
I want current time to 28/5/2012 23:59:999
May 28, 2012 at 8:18 am
Ok, lets start again
For the current day you want
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,1,GETDATE())) --DueDate > '2012-05-28 15:12:00.000' AND DueDate < '2012-05-29'
For tomorrow (if you just want only tasks due on 29-05-2012)
DueDate > CONVERT(DATE,DATEADD(DAY,1,GETDATE())) AND DueDate < CONVERT(DATE,DATEADD(DAY,2,GETDATE())) --DueDate > '2012-05-29' AND DueDate < '2012-05-30'
For tomorrow (if you want from now till midnight 29-05-2012
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,2,GETDATE())) --DueDate > '2012-05-28 15:12:00.000' AND DueDate < '2012-05-30'
For the next week (7 days from tomorrow (so Tuesday - Monday))
DueDate > CONVERT(DATE,DATEADD(DAY,1,GETDATE())) AND DueDate < CONVERT(DATE,DATEADD(DAY,8,GETDATE())) --DueDate > '2012-05-29' AND DueDate < '2012-06-05'
For the next week (from current time so Monday 15:12 for 7 days)
DueDate > GETDATE() AND DueDate < CONVERT(DATE,DATEADD(DAY,8,GETDATE())) --DueDate > '2012-05-28 15:12:00' AND DueDate < '2012-06-05'
It is a case of doing the right math and putting the right DATEDIFF,DATEADD functions in where you need them to calculate the right date ranges you class as Today, Tomorrow, This Week
May 28, 2012 at 8:43 am
This still records I dont want to see?
In all of the functions you have given I am having to change 'DATE' to 'DATETIME' do you think this is why its not returning the records I want?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply