Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1548
  • Last Modified:

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!
0
xenium
Asked:
xenium
  • 6
  • 2
1 Solution
 
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
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
xeniumAuthor Commented:
I'm thinking with integer we coudl avoid string operations completely, eg year = yyyyddd/1000, day = yyyyddd - 1000*year

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a good point, actually.

consider the modulo operator
day = yyyyddd % 1000  

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now