'How do I convert datetime hour part from 24 hour format to 12 hour format in SSIS expression?

I want to convert the current time's hour part from 24-hour format to 12-hour format. I need output in SSIS expression as the below query gives in SSMS.

SELECT '0'+CAST(DATEPART(HH,GETDATE()) AS VARCHAR)
   + REVERSE(SUBSTRING(REVERSE(CONVERT(NVARCHAR,GETDATE(),109)),1,2))

Output:

01AM

Actually, this was an example to show what is required. I'm loading data from the database table to an Excel sheet in SSIS and to name the sheet, I have added the expression:

\\\\Filespace1\\ITCommon\\Data Validation\\Data ValidationComplex Care\\Daily_Report_" 
   + (DT_WSTR,2)DATEPART("hh",GetDate())+"_" 
   + (DT_WSTR,4)DATEPART("yyyy",GetDate()) 
   + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) 
   + RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2)+".xls"

and I want to get the file name as: Daily_Report_01PM_20220414.xls instead of Daily_Report_13_20220414.xls. Hope you understand my question.

sql-serverssissql-server-2012etlintegration


Solution 1:[1]

I use script task to parse dates. Something like...

DateTime.Now.ToString("yyyyMMdd_hh:mm_tt");

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 KeithL

Related Questions