Date Problem

I have a date problem within Reporting Services.

During ETL (SSIS 2005) process we converted a date e.g. purchase date from a file

which was 20070703 to 03/07/2007 00:00:00, (this is a standalone date not associated with dim_time)

and once i ran the fact table in sql query (in sql management studio 2005) the date does indeed come out as above, 03/07/2007.

So, the cube was built and i began building reports in SSRS 2005 when the above date has gone back to 2007-07-03 00:00:00.

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Can anyone help me force the date back or am i missing something ?

Thanks

LakP wrote:

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Be more specific, how and *where* did you try to format it? It should work fine if in your textboxes etc in the report if you do a Format(CDate(<your date>), <required format>) (use it in the expression property of the textbox).

Date Problem

I have a date problem within Reporting Services.

During ETL (SSIS 2005) process we converted a date e.g. purchase date from a file

which was 20070703 to 03/07/2007 00:00:00, (this is a standalone date not associated with dim_time)

and once i ran the fact table in sql query (in sql management studio 2005) the date does indeed come out as above, 03/07/2007.

So, the cube was built and i began building reports in SSRS 2005 when the above date has gone back to 2007-07-03 00:00:00.

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Can anyone help me force the date back or am i missing something ?

Thanks

LakP wrote:

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Be more specific, how and *where* did you try to format it? It should work fine if in your textboxes etc in the report if you do a Format(CDate(<your date>), <required format>) (use it in the expression property of the textbox).

Date lookup in SSIS

How do I perform a date lookup in SSIS. I have a date with time component in it. This has to be looked-up with a table that contains only a date element.

You need convert the fields into varchar and do the comparison or you can convert both the fields to similar date formatted datetime type and do the comparison.

Thanks,

S Suresh

|||

I tried converting to varchar and it does not work well. There should be some other elegant way of doing this. To help understand the problem, I have created two tables table_1 and table_2. Table_1 is the source table with one column DateWithTime of type (datetime). Table_2 is the lookup table with columns DateSK of type (int) and another column DateAlone of type (smalldatetime).

I am taking the column DateWithTime from table_1 and looking it up with DateAlone from table_2 to get DateSK.

I do not know the right way to lookup date fields. Should I compare day, month and year separately to get DateSK.

Thanks,

Vijay

|||

I commonly use a slight cheat on this one, if you make the integer key of your lookup table the difference in days from 1 Jan 1900 then you can calculate the key instead of looking it up.

You can also use the same trick with the time portion of neccessary (do the diff in seconds).

Hope that helps you

Philip

|||

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

|||

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

|||

Philip Coupar wrote:

mike.groh wrote:

Vijay: Suresh's suggestion should have worked for you. The conversion statement will look something like this:

CONVERT( varchar, <table>.<datetimevalue>, 101 )

The "101" means to convert it to a string in US date format: mm/dd/yyyy

CONVERT supports a number of arguments for the output string -- lookup CONVERT in Books Online to see what I mean.

As Suresh suggests, you'll probably have to convert the columns in both tables to do the comparison.

Being from the UK mm/dd/yyyy does not mean too much to me as we use dd/mm/yyyy, this makes string based manipulation of date ambiguous as 01/05/2006 is either the 1st May or 5th Jan. This can either be made unambiguous by using ISO date format yyymmdd or is it yyyy-mm-dd, I can't remember offhand what the format code is for that I think it might be 121. or using names for months instead of numbers.

The reason I use the method I have already posted on this thread is it overcomes this ambiguity and provides a fast way of identifying the correct key for dates and times, which I think was the purpose of the original post.

yyyy--mm-dd is unambiguous.

Date issue with Derived Column / Expression Language

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jeff,

I don't think that's the case. I have just created a package containing a DT_DBTIMESTAMP, DT_DBDATE & DT_DATE and managed to put the value "1500-12-31" into each of those columns.

-Jamie

|||Hey Jamie, thanks for taking the time to answer....but, did you attempt a date function on any of the dates. Try doing a DATEPART("YEAR",date_col) and see what happens.|||

Hey. That function works on any date after 1753-01-01, nothing before that.

Looks like you were right!!

-Jamie

|||Great. I wanted some independent verification. I just submitted this as a bug.

Date formats in SSIS

Hi once again guys,

I seem to be struggling with everything in SSIS these days!

I have a datetime field and I want to convert it to the following format in my derived column component :

yyyy.mm.dd

I also have another datetime field but this time I am only interested in the time values and I want to get :

HH:MM

How do I go about doing this in the SSIS expression builder?

Please help.

Sometime is easier to perform this kind of transforms right on the source query; but that depends on your level of confidence when writing SQL Vs SSIS expressions. In general I find SQL syntax more readable than its equivalent of SSIS expression.|||

Hi Rafael,

You are correct of course.

I do find it a lot easier to do this kind of thing in T-SQL but I am trying to use as much of SSIS as possible.

T-SQL :

convert(char(5), getdate(), 114) gets me the time in format HH:MM

convert(varchar, getdate(), 102) gets me the date format I desire

BUT this is about SSIS expressions not T-SQL for me :)

Anyway, in the end I used a series of DATEPART functions to get the year, month and days and concatenated together to form my YYYY.MM.DD string.

Thanks for your input.

|||

dreameR.78 wrote:

the year, month and days and concatenated together to form my YYYY.MM.DD string.

I was just about to suggest that :)

its the best way when using expressions.

-Jamie

|||

Hi Jamie,

Long time no see!

Could you please tell me why the following expression doesn't get validated?!!!!!! It's driving me crazy!!!!!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" +

LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

|||Your first "else" has a string component in it (":") and cannot be evaluated to the integer of 1.

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)|||

dreameR.78 wrote:

Hi Jamie,

Long time no see!

Could you please tell me why the following expression doesn't get validated?!!!!!! It's driving me crazy!!!!!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" +

LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

Error message?

|||

Phil Brammer wrote:

Your first "else" has a string component in it (":") and cannot be evaluated to the integer of 1.

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)

Perhaps parenthesis around the last if-then-else statement?

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) + ":" + (LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ?

"0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) :

(dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime))|||

Hi Phil,

Actually I made a formatting mistake,

The exprerssion is all the 4 lines!

LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)

gets validated correctly.

All the above is aying is get the minues datepart from the datetime field and if it is on digit value then prefix it with a zero else return it the way it is.

The second part of the xpression is pretty much idetical to the above only it rteurns the seconds datepart from my datetime field.

I am concatenating : so that my time appears as MM:SS

Should be simple really but my expression is highlighted in red for some reason. But as I said, the above code on it's on, i.e. just the MM works fine, it's only when I include the second part that the expression fails to evaluate.

|||Hi again Phil, that's exactly what I tried before posting this but even though the expression evaluated. The return result was not showing the seconds. Not even a doble zero!|||

Hi Jamie,

The error message isn't very specific to be honest, but the expression is highlihted in red so obviously it doesn't like something about it.

I'm getting depressed now.

|||You have to surround your conditional statements with parenthesis and then concatenate them with the "+" symbol:

(LEN((dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("mi",btg_opty_start_datetime))

+ ":" +

(LEN((dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime)) == 1 ? "0" + (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime) : (dt_str,2,1252)DATEPART("ss",btg_opty_start_datetime))

Also, ensure that the output type of the column is string.|||

Hi Phil,

Ah sport on. Well done my friend! It's now working.

I only had brackets for the second part but when I also included them for the first part, the result came out as expected.

A good way to end the week.

Have a good weekend all.

Date Format Conversions in SSIS

Hi

I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.

I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.

any suggestions will be gratefully recieved.

Best regards

It's just string manipulation. Add a derived column transformation, and manipulate the input string (using SUBSTRING, etc.) into a new column, casting it as the appropriate type.

Greg.

Date conversion in SSIS ETL

I have date in Flat file and it is in the string format,but now i want to convert it in to normal date format.I have tried doing this by SSIS but it is not working.Are you trying to use a data conversion transform? What error are you getting?|||yes in Data flow transformation in intregartion services.I have numerously but i have not succeded|||and dat is with Double quotes.|||Please post an example of the date data and we'll try to help you from there.|||

This is example of flat file in .csv

890512 means 05/12/1989 .

When i uploda using SSIS 890512 becomes "890512" and data type is string.

I want to convert "890512" to date format but i m getting error.

|||You shouldn't have the double quotes in your data when reading a .CSV file into SSIS. If you do, then perhaps there's an error in how you set up your flat file import. Make sure the text qualifier is set to double quotes.

But before we give an example of how to work with your non-year 2000 compliant date format, what are the rules for prefixing the year with "19" or "20"?|||What is to be given in SSIS text qualifier.I can not get it.|||

Nirad Pachchigar wrote:

What is to be given in SSIS text qualifier.I can not get it.

"|||

now uploading is fine without "",but still i can not convert date into proper format.i got error on say after 6514 column.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [1]: Data conversion failed while converting column "DATE" (60) to column "Copy of DATE" (249). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [1]: The "output column "Copy of DATE" (249)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of DATE" (249)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

|||

Phil Brammer wrote:

You shouldn't have the double quotes in your data when reading a .CSV file into SSIS. If you do, then perhaps there's an error in how you set up your flat file import. Make sure the text qualifier is set to double quotes.

But before we give an example of how to work with your non-year 2000 compliant date format, what are the rules for prefixing the year with "19" or "20"?

You can't convert the date as it is, you'll need to format your string. To do that, you should be using a 4-digit year as well. Answering my previous question will help ensure you do it correctly.

However, this is how you get started:

(DT_DBDATE)(substring([datecolumn],3,2) + "/" + substring([datecolumn],5,2) + "/" + substring([datecolumn],1,2))|||

See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=506809&SiteID=1. It shows an example of using SUBSTRING to grab the components of the date from a string and convert to a date. As Phil mentioned above, you'll need to add some logic to handle what century the date belongs in.

(DT_DATE)(SUBSTRING(YourDate,3,2) + "/" + SUBSTRING(YourDate,5,2) + "/" + (SUBSTRING(YourDate,1,2) < 40 : ("20" + SUBSTRING(YourDate,1,2)), ("19" + SUBSTRING(YourDate,1,2)))

Not sure if got the syntax exactly right (don't have the IDE available right now), but it should be close.

|||

jwelch wrote:

See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=506809&SiteID=1. It shows an example of using SUBSTRING to grab the components of the date from a string and convert to a date. As Phil mentioned above, you'll need to add some logic to handle what century the date belongs in.

(DT_DATE)(SUBSTRING(YourDate,3,2) + "/" + SUBSTRING(YourDate,5,2) + "/" + (SUBSTRING(YourDate,1,2) < 40 : ("20" + SUBSTRING(YourDate,1,2)), ("19" + SUBSTRING(YourDate,1,2)))

Not sure if got the syntax exactly right (don't have the IDE available right now), but it should be close.

Real close, John. And this assumes that when the year is 0-39, you prefix it with "20" or else you prefix with "19".

Here's the corrected syntax:
(DT_DBDATE)((SUBSTRING(YourDate,3,2) + "/" +

SUBSTRING(YourDate,5,2) + "/" + ((SUBSTRING(YourDate,1,2) < 40 ?

("20" + SUBSTRING(YourDate,1,2)) : ("19" + SUBSTRING(YourDate,1,2)))))

Here's a question for the OP though. Since this is a numeric number stored as an integer and without the century indicator for the year, what happens when we are talking about March 25, 2007? Is the date listed as 70325, or 070325? This will make a difference in how you convert the date.

Date Conversion

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish D

Sounds OK. Put a data viewer immediately prior to the destination to see what the data looks like.

-Jamie

Date conversion

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

Phil,

That cannot be explicitly casted as a DT_DBTIMESTAMP because of the "-" between days and hours and the "."'s in the time. The following expression in a derived column expression should work:

(DT_DBTIMESTAMP)(SUBSTRING(<column_name>, 1,10) + " " + SUBSTRING(<column_name>, 12,2) + ":" + SUBSTRING(<column_name>, 15,2) + ":" + SUBSTRING(<column_name>, 18,2))

OK, I've done this from memory so it might not work exactly correctly but hopefully you get the idea and you can modify it appropriately.

-Jamie

|||Thanks Jamie.|||

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

|||

ckeaton wrote:

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884269&SiteID=1|||Thank you!

Date conversion

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

Phil,

That cannot be explicitly casted as a DT_DBTIMESTAMP because of the "-" between days and hours and the "."'s in the time. The following expression in a derived column expression should work:

(DT_DBTIMESTAMP)(SUBSTRING(<column_name>, 1,10) + " " + SUBSTRING(<column_name>, 12,2) + ":" + SUBSTRING(<column_name>, 15,2) + ":" + SUBSTRING(<column_name>, 18,2))

OK, I've done this from memory so it might not work exactly correctly but hopefully you get the idea and you can modify it appropriately.

-Jamie

|||Thanks Jamie.|||

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

|||

ckeaton wrote:

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884269&SiteID=1|||Thank you!