Solved

Writing Functions in T-SQL

Posted on 2009-05-07
6
224 Views
Last Modified: 2012-05-06
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
Comment
Question by:iolike
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24333141
RETURN (SELECT YEAR(@Date) )
0
 

Author Comment

by:iolike
ID: 24333195
when I  execute the following,  I don't get 08. Any suggestions

SELECT dbo.twoDigitYearPart (2008)
0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24333205
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:iolike
ID: 24333264
Althoug I execute SELECT dbo.twoDigitYearPart5 (2008), I get 05 as result.  It returns 05 regardless what year I execute
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24333298

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
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24333329
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now