October 23, 2009 at 3:30 am
I need to convert a string such as "12-OCT-2009 18:23:20" to a date datatype in either 12/10/2009 or 12/10/2009 18:23:20. Thought I could use (DT_DATE) in a derived column transformation but all it seems to do is convert it to 10/12/2009 6:23:20 PM which isn't in UK Format. Anyone know how I can change it to DD/MM/YYYY HH:MM:SS instead of MM/DD/YYYY HH:MM:SS please?
October 23, 2009 at 9:31 am
You could change the LocaleID in the advanced properties if you have the correct locale installed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 23, 2009 at 11:11 am
If you can use SQL Server to do the work:
Select Convert(Varchar(10) , Cast('12-OCT-2009 18:23:20' as Datetime), 103)
--edit - oops, misread your post. That's not a date datatype 🙁 ignore me.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2009 at 3:47 am
You could use a script component & use a few lines of VB to convert the date, e.g. something like this??
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strDate=Row.MyDate
Dim strUKDate as string=""
strUKDate=Day(strDate) & "/" & Month(strdate) & "/" & Year(strDate)
row.UKDate=strUKDate
End Sub
October 26, 2009 at 5:14 am
Jack Corbett (10/23/2009)
You could change the LocaleID in the advanced properties if you have the correct locale installed.
The locale id for the derived column transformation is English (United Kingdom) so I don't think this is the solution. The destination by the way is an excel spreadsheet if this has any influence.
October 26, 2009 at 5:36 am
Hi
I have used this
i.e U_FormatDate getdate() , 'dd/mmm/yyy'
CREATE FUNCTION [dbo].[U_FormatDate] (@Date datetime, @Format varchar(256))
RETURNS varchar(256)
AS
BEGIN
IF @Date IS NULL RETURN('')
/*
dThe one-digit of the two-digit day.
ddThe two-digit day. Single-digit day values are preceded by a zero.
dddThe three-character weekday abbreviation.
ddddThe full weekday name.
hThe one-digit or the two-digit hour in 12-hour format.
hhThe two-digit hour in 12-hour format. Single-digit values are preceded
by a zero.
HHH The one-digit or the two-digit hour in 24-hour format.
HHHH The two-digit hour in 24-hour format. Single-digit values are preceded
by a zero.
n The one-digit or the two-digit minute.
nn The two-digit minute. Single-digit values are preceded by a zero.
n The one-digit or the two-digit Second.
nn The two-digit Second. Single-digit values are preceded by a zero.
M The one-digit or the two-digit month number.
MM The two-digit month number. Single-digit values are preceded by a zero.
MMM The three-character month abbreviation.
MMMM The full month name.
t The one-letter A.M. and P.M. abbreviation (that is, “AM” is displayed as “A”).
tt The two-letter A.M. and P.M. abbreviation (that is, “AM” is displayed as “AM”).
y The year is displayed as the last two digits, but with no leading zero for any
year that is less than 10.
yy The last two digits of the year. For example, 1998 would be displayed as
“98”.
yyy The full year. For example, 1998 would be displayed as “1998”.
*/
DECLARE @d varchar(2)-- day
DECLARE @dd char(2)
DECLARE @ddd char(3)
DECLARE @dddd varchar(10)
DECLARE @hour int
DECLARE @h varchar(2)-- 12 hour
DECLARE @hh char(2)
DECLARE @HHH varchar(2)-- 24 hour
DECLARE @hhhh char(2)
DECLARE @n varchar(2)-- minutes
DECLARE @nn char(2)
DECLARE @s-2 varchar(2)-- Second
DECLARE @ss char(2)
DECLARE @m varchar(2)-- month
DECLARE @mm varchar(2)
DECLARE @MMM varchar(3)
DECLARE @MMMM varchar(10)
DECLARE @t char(1)-- am/pm
DECLARE @tt char(2)
DECLARE @y varchar(2)-- year
DECLARE @yy char(2)
DECLARE @yyy char(4)
DECLARE @year int
-- Retrieve values
SET @d = CAST(DATEPART(d, @Date) AS VARCHAR(2))
SET @dd = RIGHT('0' + @d, 2)
SET @dddd = DATENAME(dw, @Date)
SET @ddd = LEFT(@dddd, 3)
SET @Hour = DATEPART(hh, @Date)
SET @HHH = CAST(@Hour AS VARCHAR(2))
SET @hhhh = RIGHT('0' + @HHH, 2)
IF @Hour < 12 SET @tt = 'AM' ELSE SET @tt = 'PM'
SET @t = LEFT(@tt, 1)
IF @Hour > 12 SET @h = CAST(@Hour - 12 AS VARCHAR(2)) ELSE SET @h = CAST(@Hour AS VARCHAR(2))
IF @Hour = 0 OR @Hour = 12 SET @h = '12'
SET @hh = RIGHT('0' + @h, 2)
SET @n = CAST(DATEPART(n, @Date) AS VARCHAR(2))
SET @nn = RIGHT('0' + @n, 2)
SET @s-2 = CAST(DATEPART(s, @Date) AS VARCHAR(2))
SET @ss = RIGHT('0' + @s-2, 2)
SET @m = CAST(DATEPART(m, @Date) AS VARCHAR(2))
SET @MMMM = DATENAME(m, @Date)
SET @MMM = LEFT(@MMMM, 3)
SET @year = DATEPART(yyyy, @Date)
SET @y = RIGHT(CAST(@year AS VARCHAR(4)), 2)
SET @yy = RIGHT('0' + CAST(@year AS VARCHAR(4)), 2)
SET @yyy = RIGHT('000' + CAST(@year AS VARCHAR(4)), 4)
-- Parse format
SET @Format = REPLACE(@Format, 'dddd', '\\\\1\\\\')
SET @Format = REPLACE(@Format, 'ddd', '\\\\2\\\\')
SET @Format = REPLACE(@Format, 'dd', '\\\\3\\\\')
SET @Format = REPLACE(@Format, 'd', '\\\\4\\\\')
SET @Format = REPLACE(@Format, 'hhhh', '\\\\5\\\\')
SET @Format = REPLACE(@Format, 'hhh', '\\\\6\\\\')
SET @Format = REPLACE(@Format, 'hh', '\\\\7\\\\')
SET @Format = REPLACE(@Format, 'h', '\\\\8\\\\')
SET @Format = REPLACE(@Format, 'nn', '\\\\9\\\\')
SET @Format = REPLACE(@Format, 'n', '\\\\10\\\\')
SET @Format = REPLACE(@Format, 'mmmm', '\\\\11\\\\')
SET @Format = REPLACE(@Format, 'mmm', '\\\\12\\\\')
SET @Format = REPLACE(@Format, 'mm', '\\\\13\\\\')
SET @Format = REPLACE(@Format, 'm', '\\\\14\\\\')
SET @Format = REPLACE(@Format, 'tt', '\\\\15\\\\')
SET @Format = REPLACE(@Format, 't', '\\\\16\\\\')
SET @Format = REPLACE(@Format, 'yyy', '\\\\17\\\\')
SET @Format = REPLACE(@Format, 'yy', '\\\\18\\\\')
SET @Format = REPLACE(@Format, 'y', '\\\\19\\\\')
SET @Format = REPLACE(@Format, 's', '\\\\20\\\\')
SET @Format = REPLACE(@Format, 'ss', '\\\\21\\\\')
-- Populate with values
SET @Format = REPLACE(@Format, '\\\\1\\\\', @dddd)
SET @Format = REPLACE(@Format, '\\\\2\\\\', @ddd)
SET @Format = REPLACE(@Format, '\\\\3\\\\', @dd)
SET @Format = REPLACE(@Format, '\\\\4\\\\', @d)
SET @Format = REPLACE(@Format, '\\\\5\\\\', @hhhh)
SET @Format = REPLACE(@Format, '\\\\6\\\\', @hhh)
SET @Format = REPLACE(@Format, '\\\\7\\\\', @hh)
SET @Format = REPLACE(@Format, '\\\\8\\\\', @h)
SET @Format = REPLACE(@Format, '\\\\9\\\\', @nn)
SET @Format = REPLACE(@Format, '\\\\10\\\\', @n)
SET @Format = REPLACE(@Format, '\\\\11\\\\', @mmmm)
SET @Format = REPLACE(@Format, '\\\\12\\\\', @mmm)
SET @Format = REPLACE(@Format, '\\\\13\\\\', @mm)
SET @Format = REPLACE(@Format, '\\\\14\\\\', @m)
SET @Format = REPLACE(@Format, '\\\\15\\\\', @tt)
SET @Format = REPLACE(@Format, '\\\\16\\\\', @t)
SET @Format = REPLACE(@Format, '\\\\17\\\\', @yyy)
SET @Format = REPLACE(@Format, '\\\\18\\\\', @yy)
SET @Format = REPLACE(@Format, '\\\\19\\\\', @y)
SET @Format = REPLACE(@Format, '\\\\20\\\\', @s-2)
SET @Format = REPLACE(@Format, '\\\\21\\\\', @ss)
return(@Format)
END
October 26, 2009 at 5:44 am
Can you use this function from within a ssis data flow task though?
October 26, 2009 at 6:32 am
If your source is a sql statement (then you would use as part of the SQL statement..or if you can execute a SQL task that performs an update to the table ( if you using a staging table)
October 26, 2009 at 6:48 am
My source file is a pipe-delimited text file so not able to do any sql processing unless I build a staging sql table in between, which seems a little overkill. As it currently stands my data flow task has a flat file source (as mentioned above), a derived column transformation, then a lookup transformation and then an excel destination.
October 26, 2009 at 7:33 am
No Worries, as I Always create a staging table i have the benefit of doing stuff like this..
creating a staging table is always fairly simple..if you are using SSIS anyway
October 26, 2009 at 8:47 am
STOP PRESS! Interesting this. I just put a data viewer between my lookup transformation and my Excel destination and the derived column date is showing in UK format. It must be the population of the excel document that must be at fault. I'm writing to a template file with column headers and no detail rows. When I open the spreadsheet up, the cell is not formatted as a date field, it's formatted as a General field so at a loss as to how to force the data as UK format. Have given up and changed back to text. If anyone can find a fix though would be most grateful. Is this worth sending to Microsoft Connect?
October 26, 2009 at 9:12 am
Can you edit the Excel template, changing the format of the column to Date with a locale of English (United Kingdom)?
October 26, 2009 at 9:14 am
Tried that and it didn't work. Key to that really was that even when I tried to convert the value to be a date field once I created the previous version (i.e. to us format) it wouldn't let me. Not sure whether it's one of those excel trying to be clever type things!
October 26, 2009 at 9:25 am
So if you remove all display formatting in Excel, what does the field actually contain?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2009 at 9:32 am
I've only got a header row containing my column headers in the template file. Once the spreadsheet is populated the field has 10/12/2009 15:32:22 in it in a general format.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply