Function Returning DateTime datatype - need an INT

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 2008 Forums

Transact-SQL (2008)

Function Returning DateTime datatype - need an INT

Author

Topic

Jarvai1
Starting Member

1 Post

Posted - 2010-10-31 : 19:11:26

I have created the following function which is supposed to return an INT datatype, but SQL Server is telling me that it is returning a DateTime data type. Can anyone tell me what I need to change to get it to return an INT datatype?

CREATE FUNCTION [dbo].[GetWeekEndDate] (@WkDate DateTime)
returns DateTime
as begin

--Declare @WkDate Date = GetDate()
declare @WkEndDate Date = DATEADD(DAY , 7 - DATEPART(WEEKDAY,@WkDate), @WkDate) --Get Last Day Of Week
Declare @WkEndDateInteger int = YEAR(@WkEndDate) * 10000 + -- Convert Date to Int
MONTH(@WkEndDate)* 100 +
DAY (@WkEndDate)
--SELECT cast(@WkEndDateInteger AS Int) AS WeekEndDate

Return convert(int, @WkEndDateInteger)
End

Jarvai1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 19:58:54

change
returns DateTime
to
returns int

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-11-01 : 02:02:23

Todays date which is 1/11/2010 . Hence the week end date will be 6/11/2020
---------------------------------------
CREATE FUNCTION GetWeekEndDate(@WkDate DateTime)
RETURNS INT
AS
BEGIN
--Declare @WkDate Date = GetDate()
declare @WkEndDate DateTIME
SET @WkEndDate = DATEADD(DAY , 7 -
DATEPART(WEEKDAY,@WkDate), @WkDate)
--Get Last Day Of Week
Declare @WkEndDateInteger int
SET @WkEndDateInteger = YEAR(@WkEndDate) * 10000 +
-- Convert Date to Int
MONTH(@WkEndDate)* 100 +
DAY (@WkEndDate)
Return @WkEndDateInteger
END
----------------
SELECT DBO.GetWeekEndDate(GETDATE())

-----------
20101106

(1 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-01 : 04:50:56

[code]CREATE FUNCTION dbo.fnGetWeekEndDate
(
@WkDate DATETIME
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT CONVERT(CHAR(8), DATEADD(DAY, DATEDIFF(DAY, 0, '20101101'), 5), 112)
)
END[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources