Is it possible to write an sql query that returns
18315-0921

The result is time based where

18 means 2018
315 means day of the year
0921 means present time.

Per @Rob's suggesion in the comments you can do this via: Example

declare @myDateTime DateTime = getutcdate()



select format(@myDateTime, 'yy')

+ format(DatePart(dy, @myDateTime), '000')

+ format(@myDateTime, '-HHmm')

However, the ddd part isn't available this way, since ddd would convert to the weekday name: i.e per documentation. That said, you can use this to simplify the above: Example

Original solution:

Here's a solution: Example

declare @myDateTime DateTime = getutcdate()



--to get the component parts



select DatePart(YEAR, @myDateTime) - 2000 YY

, DateDiff(Day, DateFromParts(DatePart(YEAR, @myDateTime),1,1), @myDateTime) DDD

, DatePart(Hour, @myDateTime)  HH

, DatePart(Minute, @myDateTime) MM



--to format as a single string



select right('0' + cast(DatePart(YEAR, @myDateTime) - 2000 as varchar(10)), 2)

+ right('00' + cast(DateDiff(Day, DateFromParts(DatePart(YEAR, @myDateTime),1,1), @myDateTime) as varchar(10)), 3)

+ '-'

+ right('0' + cast(DatePart(Hour, @myDateTime) as varchar(10)), 2)  

+ right('0' + cast(DatePart(Minute, @myDateTime) as varchar(10)), 2) 

• 
  DatePart(YEAR, @myDateTime) - 2000 gives us a 2 digit year by getting the full year (e.g. 2018 as a number, then pulling back just those digits we're interested in by subtracting 2000)


• 
  DateDiff(Day, DateFromParts(DatePart(YEAR, @myDateTime),1,1), @myDateTime) gives us the number of days since the 1st Jan in the date's year (given by DateFromParts(DatePart(YEAR, @myDateTime),1,1)) up to the given date (so 1st Jan would be 0; if you wanted 1st Jan to be 1, just add 1 to this result).


• The hour and the minute value are just given by pulling back those date parts.


• When we create the string we cast these numeric results to strings (varchar) and concatenate them.


• The right('0' + /*...*/) part allows us to right pad the digits with leading zeros, so if the time's 09:05 we get 0905 instead of 95.

You can get almost what you're after by using the native format command; e.g.

select format(@myDateTime, 'yy-HHmm')

However, the ddd part isn't available this way, since ddd would convert to the weekday name: i.e per documentation. That said, you can use this to simplify the above: Example

select format(@myDateTime, 'yy') 

+ right('00' + cast(DateDiff(Day, DateFromParts(DatePart(YEAR, @myDateTime),1,1), @myDateTime) as varchar(10)), 3)

+ format(@myDateTime, '-HHmm') 

declare @TDATE as datetime2 = getdate();



select    Right('0' + CAST(YEAR(@TDATE) % 100 as varchar(2)) , 2) 

       +  RIGHT('00' + CAST(DATEPART(y,@TDATE) as varchar(3)) , 3)

       +  '-'

       + REPLACE(CONVERT(varchar(5),@TDATE, 114), ':', '');

so I'm getting the Year number in 2 digits by taking the modulus 100 of the year , e.g. 2014 % 100 is 14

then I'm changing that to a string, with a leading '0' if needed

To get the day number of the year, I'm using DatePart with 'y' to indicate 'day of year number' and that is padded with up to 2 digits if needed.

To get the time I'm converting to hh:mi:ss:mmm, but only keeping the first 5 chars, then I'm replacing the ':' with nothing