CODEWITHSUNDEEP
sql-serverssis
CraigBob
CraigBob

Reputation: 157

Convert GETDATE() to YYYYMMDDhhmmss as derived column

I'm trying to convert getdate to a string in YYYYMMDDHHmmss format without much luck. I need to do this in SSIS as a derived column.

I've tried using Datepart, but it's not working.

Datepart("YYYY",(GETDATE())) & datepart("MM",MONTH(GETDATE())) & DATEPART("DD",(GETDATE())) & DATEPART("HH",(GETDATE()) & DATEPART("MM",(GETDATE())) & DATEPART("SS",(GETDATE()))

Any clues what I'm doing wrong?

Upvotes: 3

Views: 19880

Answers (5)

CraigBob
CraigBob

Reputation: 157

Here's what worked for me.

(DT_STR, 4, 1252)DATEPART("YYYY", GETDATE())+(DT_STR,2,1252)DATEPART("MM", GETDATE())+(DT_STR,2,1252)DATEPART("DD", GETDATE())+(DT_STR,2,1252)DATEPART("HH", GETDATE())+(DT_STR,2,1252)DATEPART("MI", GETDATE())+(DT_STR,2,1252)DATEPART("SS", GETDATE())

Upvotes: 2

billinkc
billinkc

Reputation: 61201

DATEPART

Returns an integer representing a datepart of a date.

The operation you would like to do is string concatenation for those values. The operator for string concatenation in the SSIS Expression language is +. However, + is also the addition operator for the integer data type so if you use 

Datepart("YYYY",(GETDATE())) + datepart("MM",MONTH(GETDATE()))

You would not get 201410. Instead, you'd have a value of 2024. Now, you can use addition to get you were you want to be, you'll just need multiplication as well. (2014 * 100) + 10 will equal 201410 and returns the value as integer so perhaps that fits with what you want. However, once you build out to YYYYMMDDHHMMSS you're probably outside the bounds of Int32 and I'm too lazy to look it up, possibly Int64.

The better approach will be to cast the results of the DatePart to a string and use concatenation. But, there's still a problem there. 05 as an integer is just 5. That leading zero is an artifact of presentation so if you want it in your value, you'll need to explicitly put it there. The preferred method of doing so is to concatenate a leading 0 and then shave off the last 2 characters. For October-December, you'll have a 3 character string 010/011/012 that then gets turned back into 10/11/12. The remaining months will become 01/02/../09 and taking the right two most characters yields the correct values.

RIGHT(("0" +(DT_WSTR, 2) MONTH(GETDATE())), 2)

The & isn't a concatenation operator in this languages so that is problem #1 with your formula.

Upvotes: 2

Mark He
Mark He

Reputation: 741

This should do it, it simply casts GETDATE() as a string and replaces unnecessary characters.

LEFT(REPLACE(REPLACE(REPLACE((DT_STR,30,1252)GETDATE(),"-",""),":","")," ",""),14)

Upvotes: 2

HasaniH
HasaniH

Reputation: 8392

This should give you what you want:

REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 20), '-', ''), ':', ''), ' ', '')

See here for more date formats you can use with convert

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try this:

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "Hh" , getdate() ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , getdate() ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , getdate() ), 2)

Upvotes: 7

Related Questions