Change the date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Change the date

Author

Topic

10Dawg
Starting Member

46 Posts

Posted - 2011-06-16 : 14:16:05

The following is a code that runs successfully. If I run it at 5pm today (Thursday). It starts at midnight and lists records up to 5pm. I want it to, if I start at 5pm on Thursday, start at midnight (Tuesday night/Wednesday morning)and list all records updated for the next 20 hours, on Wednesday. I want to be able to change the date to list records for any day I wish (Yesterday, Tuesday, Monday, Last week, whatever) starting at midnight and going for the next 20 hour period.

Please advise. Thanks in advance.

DECLARE @Today smalldatetime
SET @Today = CONVERT ( varchar(12), getdate(),101)
SELECT R.DocID, I.ShortName, R.UpdatedBy, R.UpdateDate
FROM ReviewEDocIssueCode R JOIN IssueCode I on R.IssueCodeID = I.IssueCodeID
WHERE R.UpdateDate >= @Today AND R.UpdateDate <= (DateAdd(hour,20,@Today))
ORDER BY UpdateDate

10Dawg

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-16 : 15:09:52

DECLARE @start smalldatetime,@stop smalldatetime
SET @start = dateadd(day,datediff(day,0,getdate()),0)
SET @stop = dateadd(hh,20,@start)
select @start,@stop

SELECT R.DocID, I.ShortName, R.UpdatedBy, R.UpdateDate
FROM ReviewEDocIssueCode R JOIN IssueCode I on R.IssueCodeID = I.IssueCodeID
WHERE R.UpdateDate >= @start AND R.UpdateDate <= @stop
ORDER BY UpdateDate

Jim

Everyday I learn something that somebody else already knew

10Dawg
Starting Member

46 Posts

Posted - 2011-06-16 : 15:49:28

Thanks Jim but where do I put the "date of choice"? Please code it as though I want Wednesday, June 15 2011 data.

10Dawg

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-16 : 17:27:40

replace getdate() with your date of choice. All that dateadd/datediff stuff did was strip the time component from the getdate(), while keeping it as a date. You may want to make this a table-valued function or stored procedure, passing in your date of choice as a parameter.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-16 : 17:30:01

Actually, please provide some sample inputs and expected outputs, I may not be fully understanding your request.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources