Formatting a Datetime in an Azure Analytics job

Question

I need to insert the timestamp as a column in a Azure Table with the format yyyy-MM-dd-HH-mm

In plain T-SQL I would do something like:

SELECT FORMAT( GETDATE(), 'yyyy-MM-dd-HH-mm' ) AS 'PK' 

I thought the CONCAT and DATENAME/DATEPART functions would help me, and I ended up with this, but it is not producing the output I need:

CONCAT(DATENAME(yyyy,System.Timestamp),'-',CAST(DATEPART(mm,System.Timestamp) AS NVARCHAR(MAX)),'-',DATENAME(dd,System.Timestamp),'-',CAST(DATEPART(hh,System.Timestamp)  AS NVARCHAR(MAX)),'-',DATENAME(mi,System.Timestamp))

2015-10-12T05:17:37.807Z is formatted like 2015-10-12-5-17 and I am expecting 2015-10-12-17-17

Thanks in advance

Answer 1

I not sure I understand the question. datepart(hh,'2015-10-12T05:17:37.807Z') = 5 is the right behavior, it is hour Five. Why do you expect 17? datepart(hh) returns hour in 24 hour format. Below query demonstrates the 24 hour format behavior

select
    DATENAME(hh,'2015-10-12T05:17:37.807Z') HourFive,
    DATENAME(hh,'2015-10-12T17:17:37.807Z') HourSeventeen
from
foo

Output

5 17

You can do something like below to always get two digits. At the moment functions that make these kind of operations easier are not there.

with T1 as
(
select
    datepart(hh,'2015-10-12T05:17:37.807Z') [Hour]
from
foo
)

select 
  case when [Hour] <10 then concat('0',cast([Hour] as nvarchar(max))) else cast([Hour] as nvarchar(max)) end [Hour]
from 
  T1