Solved

Writing Functions in T-SQL

Posted on 2009-05-07
6
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

726 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