Solved

Writing Functions in T-SQL

Posted on 2009-05-07
6
243 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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