SSIS Date and Time concatenation

user_5748138 0
Viewed 328

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.

etl ssis time datetime sql-server
1 Answers

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))
user_70312300
Related