asked on

Writing Functions in T-SQL

I am having problems specifying the date argument in this question. Any idea

Write a function named twoDigitYearPart that accepts a datetime as an argument and returns the last two digits of the year as a char(2) type.

CREATE FUNCTION dbo.twoDigitYearPart (
 
@date date )
 
Returns Char (2)
 
as
 
BEGIN
 
RETURN (SELECT CASE WHEN @date THEN @date = @date char(2) END)
 
end

Open in new window

Aneesh

RETURN (SELECT YEAR(@Date) )

iolike

ASKER

when I execute the following, I don't get 08. Any suggestions

SELECT dbo.twoDigitYearPart (2008)

GregTSmith

The YEAR function returns an integer.

You can specify a format for the date using the CONVERT function. (101 is MM/DD/YYYY)

Try this...

CREATE FUNCTION dbo.twoDigitYearPart(@date datetime) 
 
RETURNS char(2) 
 
AS 
 
BEGIN
 
	DECLARE @output char(2) 
	SET @output = RIGHT(CONVERT(varchar, @date, 101), 2) 
	RETURN @output 
 
END 
 
GO

Open in new window

iolike

ASKER

Althoug I execute SELECT dbo.twoDigitYearPart5 (2008), I get 05 as result. It returns 05 regardless what year I execute

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GregTSmith

I wouldn't have expected the SQL Server to accept an integer. Odd...

The SQL Server seems to be returning the date representing the number of days since 1/1/1900 (for 2008 that's 6/2/1905, so the function returns '05'). You should pass a datetime value instead. (i.e. SELECT dbo.twoDigitYearPart('1/1/2008') )

Here, you can see the behavior of the SQL Server for yourself:

PRINT CONVERT(datetime, 0) 
PRINT CONVERT(datetime, 2008) 
PRINT dbo.twoDigitYearPart(GETDATE())

Open in new window