Date Picker formatting

Im sure we have all seen this error before:
"The value for the report parameter XXX is not valid for its type"

I have a report (RS 2005) that has 2 date parameters, "start" and "end".

My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end

But when I go to view the report in VS2005, I get the above error.

I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.

I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)

How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....

Hi,

I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...

Regards, Jeroen

|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...

I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.

Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).

Thanks for the tips

Date Picker formatting

Im sure we have all seen this error before:
"The value for the report parameter XXX is not valid for its type"

I have a report (RS 2005) that has 2 date parameters, "start" and "end".

My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end

But when I go to view the report in VS2005, I get the above error.

I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.

I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)

How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....

Hi,

I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...

Regards, Jeroen

|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...

I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.

Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).

Thanks for the tipssql

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.