date format YYYMMDD HH:MM:SS ssis expression

Question

date format YYYMMDD HH:MM:SS ssis expression

komal145

SSCrazy Eights

Points: 9915
More actions
July 21, 2016 at 8:59 am

#312000

hi
I need date format somethimng like this YYYMMDD HH:MM:SS
i wrote expression like below but issue is i am appending this date to filename and its throwing errors due to ":" colons. Can anyone rewrite this expression to avoid errors?
(DT_STR,4,1252) datepart("yyyy", getdate())
+ (DT_STR,2,1252) datepart("mm", getdate())
+ (DT_STR,2,1252) datepart("dd", getdate())
+" "
+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) +":"
+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) +":"
+ Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What if you remove the +":"?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

komal145 SSCrazy Eights Points: 9915 More actions · Answer 2

if i remove the colons i get this format:

2016721 102358 but requirment is to have hh:mm:ss

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

komal145 (7/21/2016)
if i remove the colons i get this format:
2016721 102358 but requirment is to have hh:mm:ss

A filename can't contain colons.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 4

Why not use a script task. Using vb.net this would be

Dim MyDate as string

MyDate = date.now().ToString("yyyyMMdd HH:mm:ss")

Then pass this out to a package variable

Dts.Variables("User::VariableName").Value = MyDate