Date Questions (yes-again) - 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

General SQL Server Forums

New to SQL Server Programming

Date Questions (yes-again)

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 09:09:23

I am using reporting services but am posting here as it is a general (and probably VERY simple) question with regards to dates.

When a user runs a report manually I am giving them the ability to select a start and end date as per their specifications. BUT they also want to schedule the very same reports to run with default dates. I created a stored procedure that will return
GetDate()-@Days
but my output includes the obvious time portion of GetDate().
The code:
ALTER PROCEDURE [dbo].[Get_Date_Minus_Parm] (@Days int)
AS
BEGIN
SELECT getdate() - @Days AS [Date Minus Days]
END

When run with @Days = 1, the result is:
2009-09-22 09:05:58.630

What I would like returned is Midnight:
2009-09-22 00:00:00.000

The reason is if the job runs during the day today and the default Start Date is yesterday I need to retrieve all data from yesterday, not just all data within 24 hours.

(and I continue to ask why Date Manipulation just HATES me)

Thanks in advance

John

"The smoke monster is just the Others doing barbecue"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-23 : 09:15:22

SELECT dateadd(day, DATEDIFF(DAY, @Days, getdate()), 0) AS [Date Minus Days]

N 56°04'39.26"
E 12°55'05.63"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-23 : 09:16:27

Just try like this.

select convert(varchar,getdate()-1,101)+' 00:00:00.000'

or

select cast(convert(varchar,getdate()-1,101) as datetime)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 09:16:30

Peso and Senthil, thank you. I knew it would be simple.

*beats head against desk AGAIN*

John

"The smoke monster is just the Others doing barbecue"

Kabila
Starting Member

33 Posts

Posted - 2009-09-23 : 09:16:37

Try this:

Alter PROCEDURE [dbo].[Get_Date_Minus_Parm] (@Days int)
AS
BEGIN
SELECT cast(convert(varchar,getdate() - @Days,106)as datetime) AS [Date Minus Days]
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 09:23:18

quote:
Originally posted by Kabila

Try this:

Alter PROCEDURE [dbo].[Get_Date_Minus_Parm] (@Days int)
AS
BEGIN
SELECT cast(convert(varchar,getdate() - @Days,106)as datetime) AS [Date Minus Days]
END

You dont need double convertions
Refer the first reply of this thread

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 09:27:56

I did use Peso's but I love the fact you all have many ways to solve the same potential problem.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 10:16:44

quote:
Originally posted by JCirocco

I did use Peso's but I love the fact you all have many ways to solve the same potential problem.

John

"The smoke monster is just the Others doing barbecue"

Ok. Another method

SELECT dateadd(day, datediff(day,0,getdate()),-@Days) AS [Date Minus Days]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources