Time string in Expression with non-military time

Who is online?  0 guests and 0 members
Home  »  Blogs  »  RussLoski  »  Time string in Expression with non-military time

Time string in Expression with non-military time

change text size: A A A

posted 5/7/2012  by RussLoski -  Views: [910] 

One of my favorite expressions is a time string expression.  I use it all the time when renaming files.  In a recent MSDN question someone asked how to create a time string that used plain time, not military time (24 hour clock).  I thought that would be easy and was going to tell the writer to find it for him/herself.  But in trying to put it together, I found it rather challenging:

(DT_WSTR,24)((DT_WSTR,8)((10000 * YEAR( GETDATE()))
 + (100 * MONTH( GETDATE())) + DAY( GETDATE())) 
+ "_" + RIGHT(REPLICATE("0",2) 
+ (DT_WSTR,6)(

(10000 * (DATEPART("Hh", GETDATE()) % 12 == 0?12:
   DATEPART("Hh", GETDATE()) % 12))

+ (100 * DATEPART("mi", GETDATE())) 
+ DATEPART("ss", GETDATE())),6) 

+  (DATEPART("Hh", GETDATE()) > 11? "PM":"AM")   )

If anyone knows of a simpler way to do this, let me know.

tags : SSIS expression, DateTime
 
0
/5
Avg: 0/5: (0 votes)

Comments (5)

Daniel
Daniel said:
I have always preferred 24 hour time for sorting reasons. The expression is considerably easier. SUBSTRING(REPLACE(REPLACE( REPLACE( (DT_WSTR, 30) GETDATE() , "-", "" ) , " ", "_" ) ,":",""),1,15) You're right, developing a 12 hour time stamp presents a greater challenge. Here is what I came up with, I can't say it is better, but it is different. DATEPART( "HH", GETDATE()) < 12? SUBSTRING(REPLACE(REPLACE( REPLACE( (DT_WSTR, 30) GETDATE() , "-", "" ) , " ", "_" ) ,":",""),1,15) + "AM" :REPLACE( SUBSTRING(REPLACE(REPLACE( REPLACE( (DT_WSTR, 30) GETDATE() , "-", "" ) , " ", "_" ) ,":",""),1,15) ,SUBSTRING(REPLACE(REPLACE( REPLACE( (DT_WSTR, 30) GETDATE() , "-", "" ) , " ", "_" ) ,":",""),10,2) ,RIGHT("0" + (DT_WSTR, 2)(DATEPART( "HH", GETDATE()) - 12),2)) + "PM"
5/7/2012
 · 
Comment
 
by
RussLoski
RussLoski said:
Very nice. Your date format is much simpler than mine. I wasn't able to get your expression to work for midnight. For noon, it displayed 00 rather than 12. I may take a hand this weekend to fix it if you don't have a chance to.
5/7/2012
 · 
Comment
 
by
RussLoski
RussLoski said:
Made a small adjustment to mine to more simply deal with the midnight/noon display: (DT_WSTR,24)((DT_WSTR,8)( (10000 * YEAR( GETDATE())) + (100 * MONTH( GETDATE())) + DAY( GETDATE())) + "_" + RIGHT(REPLICATE("0",2) + (DT_WSTR,6)( (10000 * ( 1 + (( 11 + DATEPART("Hh", GETDATE())) % 12) ) ) + (100 * DATEPART("mi", GETDATE())) + DATEPART("ss", GETDATE())),6) + (DATEPART("Hh", GETDATE()) > 11? "PM":"AM") )
5/8/2012
 · 
Comment
 
by
RussLoski
RussLoski said:
Daniel, I made one small change to your expression: SUBSTRING(REPLACE(REPLACE(REPLACE( REPLACE( (DT_WSTR, 30) GETDATE(), " 00"," 12" ) , "-", "" ) , " ", "_" ) ,":",""),1,15) + ( DATEPART("Hh",GETDATE() )<12?"AM":"PM" )
5/8/2012
 · 
Comment
 
by
Daniel
Daniel said:
Doh! Midnight & noon. I was just sort of fiddling yesterday in between a couple other projects and I did very limited testing. I like your revision to my expression, very clean.
5/8/2012
 · 
Comment
 
by
Add a new comment
Blogs RSS Feed

RussLoski's latest blog posts

Blogs RSS Feed

Latest community blog posts