Solved

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

Posted on 2007-11-20
8
1,497 Views
Last Modified: 2011-09-20
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!
0
Comment
Question by:xenium
[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
  • 6
  • 2
8 Comments
 

Author Comment

by:xenium
ID: 20324234
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
 

Author Comment

by:xenium
ID: 20324462
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20326365
your code is fine, choosing integer as data type will only add more implicit cases for the left and right functions...
0
Free eBook: Backup on AWS

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

 

Author Comment

by:xenium
ID: 20326534
I'm thinking with integer we coudl avoid string operations completely, eg year = yyyyddd/1000, day = yyyyddd - 1000*year

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 20326546
a good point, actually.

consider the modulo operator
day = yyyyddd % 1000  

0
 

Author Comment

by:xenium
ID: 20326600
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
 

Author Comment

by:xenium
ID: 20326610
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
 

Author Comment

by:xenium
ID: 22330100
no time for benchmark, sorry.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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