Trying to change the date format while casting a data type
-
I am using a (DT_WSTR,2000)GETDATE() to do a type cast within an SSIS 2005 Derived Column Transformation expression.
Current Format: 2010-06-01 14:41:11.989000000
Desired Format: MM/DD/YYYY HH:MM:SS AM/PM
I cannot figure out a way to do this. Is it possible within an expression?
-
You can start from your current format and with the use of string functions you can manipulate the string until it becomes your desired format.
But I think converting the datetime to your desired format will be much easier in T-SQL than in a Derived Column.
See http://msdn.microsoft.com/en-us/library/ms187928.aspx, under Date and Time Styles.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Check this post.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Thanks for the reference to the post. I think the article does the opposite of what I am trying to do, however. It looks like it is trying to convert a sting into an ISO-formatted date.
-
I found what I need for the month, day and year:
RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
+ (DT_WSTR,4)YEAR(GETDATE())
How do I get the time? I don't see any expressions for deriving hours and minutes.
-
here you go
(DT_WSTR, 30) (DT_DATE) GETDATE()
Output:
6/9/2010 4:53:30 PM
Desired Format: MM/DD/YYYY HH:MM:SS AM/PM
-
Thank you, thank you, thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply