Showing posts with label process. Show all posts
Showing posts with label process. Show all posts

Sunday, March 25, 2012

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).

Sunday, February 19, 2012

Date Conversion throws truncation error

Hi,

I am trying to process a flat file feed. My date is in format of YYYYMMDD. The database column is of datatype "datetime". I have tried using all date related data types on FLAT FILE Connection Manager. I have also tried using Data Conversion Component. No luck so far!!

Any suggestion?

Thanks in advance,
-AnandTry the Derived Column Transformation.

This post doesn't solve exactly the same problem but its very very similar: http://blogs.conchango.com/jamiethomson/archive/2005/07/26/1867.aspx

-Jamie|||

The key point is, yyyymmdd cannot be casted to date type. (This has been bugged I believe, but feel free to log again it as I know I want it!). Using the Derived Column to crack it into format that can then be casted is the only route. I did test what was suppoted and what wasn't, but seem to have lost the packages for now....

I would also check you cracked format works for US/UK locales, as the flip-flop between the two can really mess you up. I like dd mmm yyyy myself, as no languages muck that up, not even VBScript! This may help as a start if you follow that format-

(MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

|||Hi,

I have partial success with "Data Conversion". What should I do for the column which may contain '' or null?

One more thing: Is dt_dbdate in SSIS compatible with datetime in SQL Server 2005?

-Anand|||Hi,

Will this work in the "Derived Column"?

ISNULL([Maturity Date]) ? : (DT_DBDATE)(SUBSTRING(TRIM([Maturity Date]),5,2) + SUBSTRING(TRIM([Maturity Date]),6,2) + SUBSTRING(TRIM([Maturity Date]),1,4))|||I have resolved the issue using following "Derived Column" statement:

(TRIM([Maturity Date]) == "") ? NULL(DT_WSTR,8) : SUBSTRING(TRIM([Maturity Date]),5,2) + "/" + SUBSTRING(TRIM([Maturity Date]),7,2) + "/" + SUBSTRING(TRIM([Maturity Date]),1,4)

Cheers,
Anand

Big Smile