Changing Date

Hello all, I have a question. I am fairly new to all of this, so bear with me if it is something simple (as I kinda hope it is).

In SSRS, I have a report that runs against a SQL '05 DB. The DB tables are created with a SSIS package gathering information from an AS/400 DB2 database.

The package and report run fine. One of the columns in the table is for a date (date of birth). The SSIS package gathers the column information and inserts it into SQL Server as a Decimal datatype (Decimal 6,0). The dates (decimals?) are now in the format 40207, as they were on the 400, where that specific date would be April 2, 2007. 120707 would be December 7, 2007 and so on. I would like to format the date to be mm/dd/yy or even m/dd/yy for the "single digit" months in the report for easy readability.

I have tried setting the format options in reporting services for formatting the field as a date, and even using the cdate conversion or FormatDateTime in an expression. I cannot seem to get the date to change. I either get an "# error" for the values of the fields or I get the same date that already was there (40207)?

It seems as if none of the date formatting options are working. Is this something I need to do on the package (set the mappings on the create table to create the columns as a Date, not Numeric or something similar?), or should it be easier than that by converting the output of the report to display mm/dd/yy with the "/"?

Any help is greatly appreciated.

Thanks

Expression-> Common Functions -> Date & Time -> Month || Day || Year|||

Although I am confused as to why you are storing a date value in a decimal field, this can be shown correctly via the report. Try this from your select query:

left(convert(varchar, <datefield>), len(convert(varchar, <datefield>)) - 4) + '/'
+ left(right(convert(varchar, <datefield>), 4), 2) + '/'
+ right(convert(varchar, <datefield>), 2)

Or...

reverse(stuff(stuff(reverse(<datefield>), 3, 0, '/'), 6, 0, '/'))

However, I would recommend that you update the SSIS package to handle the dates and put them in a datetime field.

Hope this helps.

Jarret

|||

Thanks for the replies.

Kenny - I don't have that option, although they are there individually. Are you meaning that I type that in along with something like (=Fields!DOB(etc))

If so, I may be doing it incorrectly, but I cannot get any of the "common" functions to work with my date/time stuff. I did however have good luck integrating the "globals" on my page (execution time, etc).

Jarrett, thanks for the reply, I haven't got to trying that part yet.

Thanks again everyone.

|||

The Month, Day, & Year functions would work if your field was already a date, but it's not, it's a decimal.

The functions that Kenny mentioned require that you pass in a date type. If you try to use them by passing a decimal type, you will get the following error:

Conversion from type 'Decimal' to type 'Date' is not valid.

Jarret

|||

Thanks. How should I put it in the SSIS package to convert? I tried using a data transformation, but got an error. The data is stored in the DB2 database as a decimal, I was just bringing it over with a simple SSIS package.

Thanks for your help.

|||

Within SSIS, in your source connection's SQL command, add a column with the statement:

reverse(stuff(stuff(reverse(convert(varchar(6), <datefield>)), 3, 0, '/'), 6, 0, '/')) as DecimalDate

Then, in your SQL database destination, map DecimalDate (instead of <datefield>) to your date type field in your SQL table.

Hope this helps.

Jarret

|||Thanks.|||

Did this fix your issue?

Jarret

|||

Yes, I can use the reverse method you described above and produce the results I want. However, I have not been successful in making the dates come over in the SSIS package. I must say though, I have been busy and haven't been completly dedicated to that recently.

Thanks for your help.