convert three fields into one datetime

Question

Post reply

convert three fields into one datetime

PHXHoward

SSCrazy Eights

Points: 8389
More actions
May 13, 2010 at 4:37 pm

#222507

Hi everyone. I've searched up, down, left, and right but I can't make sense of the answers that I've found.
I'm building an SSIS package with a flat file data source that has three fields (Year, Month, and Day).
From this source, I'm trying to create a derived column that will turn my Year, Month, and Day into a single datetime field which which I can insert into the database destination.
I've tried concatenating them into one field and allowing the database to make it a datetime upon insert but that did not work. I've also tried casting and converting but clearly don't have the syntax right. I then tried to use datepart to specify that year is the year, month is the month and day is the day.
Is someone able to steer me the right direction of this one? Thanks much.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If you define your sour columns as character you might be able to use the following:

SELECT CAST('2010'+'-'+'1'+'-'+'15' AS DATETIME)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

Thanks Lutz, I tried to do this using my three field names but it did not go through.

TITLE: Microsoft Visual Studio

------------------------------

Error at Data Flow Task [Derived Column [46]]: Attempt to parse the expression "SELECT CAST([Year]+'-'+[Month]+'-'+[Day] AS DATETIME)" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Error at Data Flow Task [Derived Column [46]]: Cannot parse the expression "SELECT CAST([Year]+'-'+[Month]+'-'+[Day] AS DATETIME)". The expression was not valid, or there is an out-of-memory error.

WayneS SSC Guru Points: 95460 More actions · Answer 3

With variables Year (int32), Month (int32), Day (int32), use this:

(DT_DATE) (

(DT_STR, 4, 1252) @[User::Year] + "-" +

(DT_STR, 2, 1252) @[User::Month] + "-" +

(DT_STR, 2, 1252) @[User::Day]

)

Obviously, just change the user variables to the fields from your data source.

If Month is like "July", just set that variable to String, and it still works fine.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

Thanks much for the replies! This worked perfectly.

Something new to add to my collection of useful SQL.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 5

What can I do for nulls? The database file allows NULLS for the datetime but in the flat file source, nulls are represented as 00 in year, month, and day fields. When I try to append, the package fails.

Can I say in the derived column to do the above unless the Year field contains 00, in which case it is NULL?

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 6

I came up with this as an if but it isn't working right now.

([YearE]== '00' ? NULL :(DT_DATE)

((DT_STR, 4, 1252) [YearE] + "-" +

(DT_STR, 2, 1252) [MonthE] + "-" +

(DT_STR, 2, 1252) [DayE]))

YearE, MonthE, and DayE are the columns I am working with.

WayneS SSC Guru Points: 95460 More actions · Answer 7

PHXHoward (5/13/2010)
I came up with this as an if but it isn't working right now.
([YearE]== '00' ? NULL : (DT_DATE)
((DT_STR, 4, 1252) [YearE] + "-" +
(DT_STR, 2, 1252) [MonthE] + "-" +
(DT_STR, 2, 1252) [DayE]))
YearE, MonthE, and DayE are the columns I am working with.

Okay, we're getting beyond what I do in SSIS. I do think that the NULL should be NULL(DT_DATE). But even with this, I'm still getting an error and don't know what to do about it. It appears to be evaluating both sides of the equation.

I'll be watching this to see what I can learn!

Edit: I think that the single-quotes around the 00 should be double-quotes: "00"

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 8

Definitely, the string value should be inserted between double quotes and try this

(DT_DBDATE)(DT_STR,10,1252)(YearE == "00" ? "" : ((DT_STR,4,1252)YearE + "-" + (DT_STR,2,1252)MonthE + "-" + (DT_STR,2,1252)DayE))

the most important thing here is to set Configure error output on the Derived column, set it to Ignore Failure in the Error tab, that will bring in NULL values in case of bad data/date values.. Let me know how it goes..

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 9

Wayne,

Thanks for responding. Your suggestions were correct because now SSIS allows me to hit ok to save the expression whereas before it did not.

Unfortunately, it still chokes when it gets a date that should be represented as NULL. I'll keep working on a solution.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 10

Thanks for that divyanth,

The error output change allowed the success.

Very exciting!

divyanth (5/14/2010)
Definitely, the string value should be inserted between double quotes and try this
(DT_DBDATE)(DT_STR,10,1252)(YearE == "00" ? "" : ((DT_STR,4,1252)YearE + "-" + (DT_STR,2,1252)MonthE + "-" + (DT_STR,2,1252)DayE))
the most important thing here is to set Configure error output on the Derived column, set it to Ignore Failure in the Error tab, that will bring in NULL values in case of bad data/date values.. Let me know how it goes..