• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

0
iolike
Asked:
iolike
  • 2
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
RETURN (SELECT YEAR(@Date) )
0
 
iolikeAuthor Commented:
when I  execute the following,  I don't get 08. Any suggestions

SELECT dbo.twoDigitYearPart (2008)
0
 
GregTSmithCommented:
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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
iolikeAuthor Commented:
Althoug I execute SELECT dbo.twoDigitYearPart5 (2008), I get 05 as result.  It returns 05 regardless what year I execute
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

CREATE FUNCTION dbo.twoDigitYearPart (
@date date )
Returns Char (2)
as
BEGIN
RETURN (SELECT RIGHT( YEAR(@Date),2) )
end
go
SELECT dbo.twoDigitYearPart('2008-02-02')
0
 
GregTSmithCommented:
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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now