return date only in stored procedure..help!

Question

Post reply

return date only in stored procedure..help!

Bill-329867

SSC Veteran

Points: 265
More actions
July 1, 2006 at 10:27 am

#97192

Hi,

I created this stored procedure to add 90 days to the current date, and return the new date:

CREATE PROCEDURE Get90Days
AS
SELECT DATEADD(day, 90, current_timestamp) AS "RETURNDAYS"

The procedure above returns this result:

2006-09-29 07:31:14.477

I need this procedure to return the date, only, like this:

09-29-2006

I tried using the convert switch to reformat the result to the date only, but have not been successful.

What does this stored procedure need to look like to return just the date, like this:

09-29-2006

I appreciate your help.
Thanks,
Bill

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

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

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

SELECT CONVERT(varchar(10), current_timestamp, 110)

Not sure if you still need a stored procedure.

Bill-329867 SSC Veteran Points: 265 More actions · Answer 2

Hi Journeyman,

Thank you!

I do still need a stored procedure. Would it look like this:

CREATE PROCEDURE Get90Days

AS

SELECT CONVERT(VARCHAR(10), DATEADD(day, 90, GETDATE()), 110) ReturnDays

Thanks,

Bill

Jeff Moden SSC Guru Points: 1004370 More actions · Answer 3

My recommendation would be not to make a trip to a character based date and back again... I'd also not make a stored procedure for it. I'd likely do it directly in the code so I don't have the overhead of a stored procedure call or a function call. However, if you really want it to be very easy to use and you insist on not putting it directly into code, I'd make a User Defined Function like this...

CREATE FUNCTION dbo.Add90Days(@MyDate DATETIME)

RETURNS DATETIME

AS

BEGIN

RETURN DATEADD(dd,90,DATEDIFF(dd,0,@MyDate))

END

If you want more flexibility, here's one where you pass the date and the number of days to add...

CREATE FUNCTION dbo.AddDays(@MyDate DATETIME, @Days INT)

RETURNS DATETIME

AS

BEGIN

RETURN DATEADD(dd,@Days,DATEDIFF(dd,0,@MyDate))

END

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 4

small adjustment to get rid of time portion..

CREATE FUNCTION dbo.AddDays(@MyDate DATETIME, @Days INT)

RETURNS DATETIME

AS

BEGIN

RETURN cast(floor(cast(DATEADD(dd,@Days,DATEDIFF(dd,0,@MyDate))as float))as smalldatetime)

END

www.sql-library.com[/url]

Bill-329867 SSC Veteran Points: 265 More actions · Answer 5

Hi Jeff,

Thank you for your help, and your suggestions; they are great!

I will try it in code.

I appreciate your help, Jeff,

Bill

Bill-329867 SSC Veteran Points: 265 More actions · Answer 6

Hi Jeff,

Thank you for your help, and your suggestions; they are great!

I will try it in code.

I appreciate your help, Jeff,

Bill

Bill-329867 SSC Veteran Points: 265 More actions · Answer 7

Hi Jeff,

Thank you for your help, and your suggestions; they are great!

I will try it in code.

I appreciate your help, Jeff,

Bill

Jeff Moden SSC Guru Points: 1004370 More actions · Answer 8

Jules,

The two functions I wrote do get rid of the time portion and they do it with only trwo functions instead of the 5 you used. AND, they did it without using even a single CAST.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004370 More actions · Answer 9

Thanks, Bill,

I do have to emphasize that performance will usually be better if you do this simple type of thing in the code mainstream instead of a function. Functions are indeed very useful for standardizing the way people do things and making code very easy to read but it's usually (not always) at the cost of some performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 10

ah yes didnt bother to test it. Nice use of data diff..

www.sql-library.com[/url]