Use SSRS calendar from date field formated as 20141010

Question

Use SSRS calendar from date field formated as 20141010

alex_martinez

SSCrazy

Points: 2144
More actions
November 10, 2014 at 4:24 pm

#397627

Hello,
I am using 2008 version of SSRS in my query I have a date field formatted as 20141110, which is November 10, 2014 how can I have the SSRS calendar pick up the date from this formatted field. I am using db2 database. Thank you in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63488 More actions · Answer 1

If SSRS is seeing the value as a string and not a date, you could create a calculated field in your dataset and set the value to something like this:

=DATESERIAL(CInt(LEFT(Fields!SomeDate.Value,4)),CInt(MID(Fields!SomeDate.Value,5,2)),CINT(RIGHT(Fields!SomeDate.Value,2)))

If you replace "SomeDate" with the field name you're fixing, you should be off to the races, because that will return a proper date, which you can sort/filter on.

HTH,

Pieter

alex_martinez SSCrazy Points: 2144 More actions · Answer 2

Thank you very much I will give that a try. I really appreciate your help.:-)

alex_martinez SSCrazy Points: 2144 More actions · Answer 3

Hi pietlinden,

I am a little confused I was able to do the calculated field without an issue, Currently my parameters are pointing to the field called invoice date (format is "yyyymmdd" ) do I point my parameters to the calculated field? I am not sure if that will work, please advise. Thank you.

pietlinden SSC Guru Points: 63488 More actions · Answer 4

you would point to the calculated date if you wanted to do date calculations, comparisons etc. The 20040201 looks like a date (to you, not to SQL Server), so it gets interpreted as a string.