Part II: Function to convert date from text format YYYYDDD where DDD = day of year

eg. select dbo.CnvDateYYYYDDD('2007032')  will return 1-Feb-2007 as a date

Can you pls update the following so that DateTime format is returned? http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22973663.html

Thanks!
xeniumAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
a good point, actually.

consider the modulo operator
day = yyyyddd % 1000  

0
 
xeniumAuthor Commented:
i think its just this:

alter FUNCTION dbo.CnvDateYYYYDDD(
    @yyyyddd varchar(7)
)
RETURNS datetime
AS
BEGIN
RETURN     DATEADD(DAY, CAST(RIGHT(@yyyyddd, 3) AS INT) - 1, LEFT(@yyyyddd, 4) + '0101')
   
END
0
 
xeniumAuthor Commented:
Since efficiency was a theme, might there be a faster way to code this if the input were an integer ?
eg select dbo.CnvDateYYYYDDD(2007032)

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your code is fine, choosing integer as data type will only add more implicit cases for the left and right functions...
0
 
xeniumAuthor Commented:
I'm thinking with integer we coudl avoid string operations completely, eg year = yyyyddd/1000, day = yyyyddd - 1000*year

0
 
xeniumAuthor Commented:
Ok thanks. Here then is the integer version of the function:


alter FUNCTION dbo.CnvDateYYYYDDD(
    @yyyyddd int
)
RETURNS datetime
AS
BEGIN
RETURN     DATEADD(DAY, @yyyyddd % 1000 - 1, DATEADD(YEAR,@yyyyddd/1000 -1900,0))
END

0
 
xeniumAuthor Commented:
I'm leaving this open a bit, coz i may just do a benchmark test vs the string functions, and note the results here for the record.
0
 
xeniumAuthor Commented:
no time for benchmark, sorry.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.