CODEWITHSUNDEEP
sql-serverdatetimetimessisetl
Will
Will

Reputation: 232

SSIS Date and Time concatenation

I have a client who wants to show a specific date (1754-01-01) but with the current time into a SQL table format of DateTime

I don't have any time column from my flat-file to reference and so it will be either GETDATE() or @StartTime from the system variables (unless there is another way).

I have been down so many avenues with this, from DT_DBTIMESTAMP with the concatenation of date and formatted GETDATE(), formatted @starttime and similar with (DT_DBTIME) trying to remove the date from the beginning. and I feel I've exhausted my knowledge of SSIS.

I'm hoping that someone has had this before or knows how I could resolve it.

Upvotes: 2

Views: 1005

Answers (1)

Hadi
Hadi

Reputation: 37313

You can use the DATEPART() function to extract hours, minutes, and seconds from the current date. Try adding the following expression to the date value, this expression extracts the time value from the current date in the following format hh:mm:ss.

RIGHT("0" + (DT_WSTR,50)DATEPART("hh",GETDATE()),2) + ":" + RIGHT("0" + (DT_WSTR,50)DATEPART("mm",GETDATE()),2) + ":" + RIGHT("0" + (DT_WSTR,50)DATEPART("ss",GETDATE()),2))

The whole expression should look like the following:

(DT_DBTIMESTAMP)("1754-01-01 " + RIGHT("0" + (DT_WSTR,50)DATEPART("hh",GETDATE()),2) + ":" + RIGHT("0" + (DT_WSTR,50)DATEPART("mm",GETDATE()),2) + ":" + RIGHT("0" + (DT_WSTR,50)DATEPART("ss",GETDATE()),2))

Upvotes: 1

Related Questions