-1

I have several datetime field which I need the time to be in 24 hour format.

Interval Start Time 2016-01-04 03.30

I need its as

Interval Start Time 2016-01-04 15.30

I developed this code in Derived Column in SSIS. But I don't think this is correct:

(DATEPART("Hh",[Interval Start Time]) == 01 ? "13" : 
    (DATEPART("Hh",[Interval Start Time]) == 02 ? "14" :
        (DATEPART("Hh",[Interval Start Time]) == 03 ? "15" :
            (DATEPART("Hh",[Interval Start Time]) == 04 ? "16" : 
            (DATEPART("Hh",[Interval Start Time]) == 05 ? "17" : 
                "")))))

I would appreciate any help.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Djbril
  • 745
  • 6
  • 26
  • 48

4 Answers4

0

Just use CONVERT:

SELECT CONVERT(char(16), [Interval Start Time], 120)

If you want to have the time delimiter a dot (.) instead of a colon (:), you can add REPLACE:

SELECT REPLACE(CONVERT(char(16), [Interval Start Time], 120), ':', '.')

btw, Datetime data type doesn't store display format.

Graham
  • 7,431
  • 18
  • 59
  • 84
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • @ Zohar the answer you provided: SELECT CONVERT(char(16), [Interval Start Time], 120) doesn't work. I still get the time 01:30 when I should get 13.30. – Djbril Jul 28 '16 at 15:25
  • Are you sure? [check this online demo.](http://rextester.com/VKLN35066) – Zohar Peled Jul 28 '16 at 16:52
0

Looks like you just need to add 12 hours to each datetime, E.g.

DATEADD(HOUR, 12, [Interval Start Time])

Or, to replicate the logic in your question you could apply the addition conditionally:

CASE WHEN DATEPART(HOUR, [Interval Start Time]) <= 5 THEN [Interval Start Time] ELSE DATEADD(HOUR, 12, [Interval Start Time]) END
Chris Pickford
  • 8,642
  • 5
  • 42
  • 73
0

If you are trying to convert the datetime to 24h format then you can try the below query. 

select CONVERT(VARCHAR(24),getdate(),121);
Or
select CONVERT(datetime,getdate(),121);

OUTPUT : 2016-07-28 20:14:10.937
Satish Kumar
  • 203
  • 3
  • 8
  • 1. better use `char` for a fixed length string instead of `varchar`. 2. the time resolution in the question is minutes, no point of showing a time resolution of milliseconds. 3. your second statement converts a `datetime` to `datetime`. I'm guessing you meant to convert it to `varchar`... – Zohar Peled Jul 28 '16 at 14:52
  • I wanted to do this in derived column in SSIS. – Djbril Jul 28 '16 at 15:07
0

try this:

(DT_WSTR,4)YEAR([Interval Start Time])+"-"+
RIGHT("0" + (DT_WSTR,2) MONTH([Interval Start Time]),2)+ "-"+ 
RIGHT( "0" + (DT_WSTR,2) DAY([Interval Start Time]),2) +" "+
(datepart("hour",[Interval Start Time]) == 1 ? "13" : 
datepart("hour",[Interval Start Time]) == 2 ? "14" :
datepart("hour",[Interval Start Time]) == 3 ? "15" : 
datepart("hour",[Interval Start Time]) == 4 ? "16" :  
datepart("hour",[Interval Start Time]) == 5 ? "17" : "" )
+"."+RIGHT( "0" + (DT_WSTR,2) datepart("mi",[Interval Start Time]),2)

thinking you were looking for the expression for the SSIS task 'Derived Column' and supposing that 'Interval Start Time' is datetime. I hope this help.

ɐlǝx
  • 1,384
  • 2
  • 17
  • 22
  • This doesn't work. I get an error. "Error at the Data Flow Task: The function "Year" does not support the data type "DT_WSTR". – Djbril Jul 29 '16 at 09:38
  • it is a simple cast , it always works..Are you using Expression in Derived Column? – ɐlǝx Jul 29 '16 at 09:47
  • The 'Interval Start Time' is already a datetime format which I used: (DT_DBTIMESTAMP)[Interval Start Time] in the derived column. – Djbril Jul 29 '16 at 10:01
  • What I have done is to rewrite [Interval Start Time] starting from the year etc ... you must enter the cast to DT_WSTR because there are characters in the variable as a '+', in this way it recreates the date with the desired format...so no need to use the cast (DT_DBTIMESTAMP)[Interval Start Time] in Expression builder. – ɐlǝx Jul 29 '16 at 10:19