Convert DD/MM/YY to YYDDD - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Convert DD/MM/YY to YYDDD

Author

Topic

Jimbojames30
Starting Member

8 Posts

Posted - 2014-11-03 : 16:43:19

Hi please can someone help me with a problem i have

i wish to convert a date for example 2014-01-01 to 14001

the date to convert to is YY / number of days into the year so if the date was 2014-12-31 i need it to read 14365

please can someone pont me in the right direction

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-03 : 17:20:15

Try this:
declare @d datetime

set @d = '12/31/2014'

select right(convert(char(4), year(@d)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@d))), @d+1)), 3)

Please note that @d is used just for testing. Replace with your column and table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/