November 23, 2012 at 12:05 am
My input from SQL Server Column x (contains 201205,201206..etc) varchar(10) and i want to load into SQL Server Last Day of month as Date ...pls make expression for derived column.Any early response would be appreciable.
Thanks
November 23, 2012 at 1:25 am
Something like this:
DATEADD("dd",-1,DATEADD("mm",1,(DB_DATE)(myColumn + "01")))
edit: it's possible you'll have to do some more string manipulation on your data column before SSIS recognizes it as a date. SSIS is a bit pedantic about that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 23, 2012 at 2:20 am
I'm sure there's a better way of doing this but my first instinctive thought would be to use a CASE statement as a computed column here.
I.e. something like:
DECLARE @dateTable TABLE (theYear INT, theMonth INT, theDay INT AS
( SELECT CASEWHEN theMonth IN (9,4,6,11) THEN 30 -- '30 days hath September...'
WHEN theMonth IN (2) THEN
( SELECT CASE WHEN theYear % 400 = 0 OR theYear % 4 = 0 THEN 29 ELSE 28 END FROM @dateTable ) -- indicates leap year
ELSE 31 END -- '...all the rest have 31...' )
FROM @dateTable
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 23, 2012 at 2:22 am
Ignore my post above. Running this you get:
[font="Courier New"] Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.[/font]
My fault entirely for not testing - not tasted my coffee this morning!
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 23, 2012 at 2:31 am
derek.colley (11/23/2012)
I'm sure there's a better way of doing this but my first instinctive thought would be to use a CASE statement as a computed column here.I.e. something like:
DECLARE @dateTable TABLE (theYear INT, theMonth INT, theDay INT AS
( SELECT CASEWHEN theMonth IN (9,4,6,11) THEN 30 -- '30 days hath September...'
WHEN theMonth IN (2) THEN
( SELECT CASE WHEN theYear % 400 = 0 OR theYear % 4 = 0 THEN 29 ELSE 28 END FROM @dateTable ) -- indicates leap year
ELSE 31 END -- '...all the rest have 31...' )
FROM @dateTable
Aside from the fact that this question is about SSIS and not TSQL, how is this better?
You have to take into account every possible rule of leap years and construct a nested case statement with in total 5 branches. My code uses 2 date functions and 1 conversion function and it lets SQL Server worry about leap years instead of yourself.
edit: seems I need coffee myself 😀 Your "I'm sure there's a better way of doing this" meant your code, not mine. Moar coffee!!! Good thing it's a Friday 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 23, 2012 at 3:19 am
My apologies Koen, it wasn't an insult to your code, but mine. And you have a point, yes - SQL Server should be sorting out the date format rather than using a custom algorithm. And a computed column using non-scalar criteria evidently doesn't work anyway.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 23, 2012 at 3:29 am
derek.colley (11/23/2012)
My apologies Koen, it wasn't an insult to your code, but mine. And you have a point, yes - SQL Server should be sorting out the date format rather than using a custom algorithm. And a computed column using non-scalar criteria evidently doesn't work anyway.
Well, SQL Server 2012 does introduce the EOMONTH function, which makes all this redundant.
Now we just need to upgrade to 2012 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 23, 2012 at 3:52 am
That's new to me - hopefully this will be useful though! There's a few functions and things coming in with 2012 which seem ... well a bit 'non-relational'. Like LAG and LEAD, for example - functions to get offset rows from a SELECT. There's also supposed to be better integration with Hadoop and other NoSQL stuff too.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 23, 2012 at 3:55 am
LAG and LEAD are still relational I think, because you use them in the OVER clause which defines a window (which has an ORDER BY).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 23, 2012 at 4:50 am
Hi, friends thanks for your response...at last i got solution for this
Derived Column1 :SUBSTRING(yourcolumn,1,4) + "-" + SUBSTRING(yourcolumn,5,2) + "-" + "01"
DerivedCloumn2:DATEADD("DAY",-1,DATEADD("MONTH",1,(DT_DBDATE)Derived Column1 Name))
.........it's working for me...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply