August 11, 2014 at 2:24 am
hi
Im using the following code to arrange dates the way I want it
here is the code
(MONTH(MaturityDate) < 10 ? "0" + (DT_WSTR,2)MONTH(MaturityDate) + "/" : (DT_WSTR,2)MONTH(MaturityDate) + "/") + (DAY(MaturityDate) < 10 ? "0" + (DT_WSTR,2)DAY(MaturityDate) + "/" : (DT_WSTR,2)DAY(MaturityDate) + "/") + (DT_WSTR,4)DATEPART("yyyy",MaturityDate)
it seems to be working fine for the most part but the problem it when I hit the 12/01/2014 it hits a problem. it writes it out as the 01/12/2014 anyone know how I can fix this problem
August 11, 2014 at 2:28 am
You are storing this into a string column?
Or do you write it to a SQL Server database?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 2:31 am
I write it to an sql database table
August 11, 2014 at 2:33 am
ronan.healy (8/11/2014)
I write it to an sql database table
Which data type does the column have?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 2:37 am
datetime
August 11, 2014 at 2:41 am
Why don't you leave the current date from the source - MaturityDate - as it is and insert that into the database.
You can convert it to the format you like when you read it (in the SELECT statement) or in the report.
You can use the third parameter of the CONVERT function to format your datetime values.
A database is for storing values, not for formatting.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 2:44 am
the formatting happens in a derived column before it hit the database.
I didn't set it up like this. this is how someone else built the package and I don't want to really mess around with it as im not fully sure what it all does(never seen a spec for it). I just need to fix the date issues for a client as quickly as possible and the code seem to work for all the other dates that come in just not for that one
August 11, 2014 at 2:49 am
The expression looks fine to me.
So you are saying the issue happens for a single date, but not for all the other dates? Seems quite odd to me.
Check the regional settings of the package to see how SSIS deals with the dates.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 2:56 am
I think it has something to do with the day and month. I think because neither go over the 12 it is getting confused I think anyway.
here is a few example of the output
12/01/2015
07/25/2012
07/25/2032
07/25/2020
04/15/2029
but the 1st one should come out as 01/12/2015
would the regional setting not affect all the dates?
August 11, 2014 at 3:01 am
It should affect all dates I assume.
Maybe because the other dates are impossible in a given format (months cannot be over 12), SSIS assumes another format?
Usually I steer away from date handling in SSIS, and this is one of the reasons.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 3:03 am
ok would you know any quick fix for this even a script or something to fix it
August 11, 2014 at 3:10 am
ronan.healy (8/11/2014)
ok would you know any quick fix for this even a script or something to fix it
Create another derived column in 'YYYYMMDD' format and INSERT that instead.
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
August 11, 2014 at 3:36 am
is that not what the code i posted is doing
August 11, 2014 at 3:52 am
No, your code takes a date and creates a mmddyyyy representation.
But if SSIS mixes up days and months, it can lead to the same issue (e.g. creating yyyyddmm by accident instead of yyyymmdd).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 4:23 am
a right ya sorry was getting mixed up.
but the date has to be in the mmddyyyy for the system it goes into when the package runs
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply