Datatime order by Problem

Hi everybody,
I have onde table called (TB) with two fields : F1(int) and F2(varchar:10)
In this table the field F2 is used to store dates in the format
dd/mm/yyyy (27/12/2002). Its is record as varchar and undesired.
I need to select all records from a single id from the field F1 and
then order by the result set by date to result set be this way:

F1 F2
------------
01 15/12/1975
01 15/12/1980
01 16/12/1998
01 27/12/2003
------------

To do this Im using the follow syntax :

SELECT * FROM TB
WHERE (F1 = '01')
ORDER BY CAST(F2 AS datetime(103))

But it gives the follow message erro trying to select data :

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

How can I convert the format used in the select to order by date type in a SQL Server range datetype to get a result set as above?

Thansk for attention.

Leonardo AlmeidaUse convert(datetime, f2, 103) instead of the cast.sql

DataTime as parameters: Help Needed.

Hello all,
I need to generate a report based on dates(from and to). Is there
anyway I can include a dropdown of datetime in the parameters of the RS
interface.
for example like from mm/dd/yyyy to mm/dd/yyyy.
I have RS 2003 EE with SP1 and have no plans of installing SP2.
Any help will be appreciated.
Thanks a lot
RaviRavi,
You need to create a list of dates as a DS, and then add two params to the
report (well, just add them to the DS for the report itself) and configure
the Params to be query selections from the Dates DS.
Using Adventurewroks DB, the following suffices for selecting particular
records of SalesOrderID, ModifiedDate columns from the SalesOrderDetail
table, by date.
DataSets:
DSDates:
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
ORDER BY ModifiedDate
DS1:
SELECT SalesOrderID, ModifiedDate
FROM dbo.SalesOrderDetail
WHERE (ModifiedDate > @.STARTDATE AND ModifiedDate < @.ENDDATE)
Form:
Table with DS1 as it's source.
Remember to edit the params in Report-Report Parameters, for both @.STARTDATE
AND @.ENDDATE, to be;
From Query; Dataset:DSDates;ValueField:ModifiedDate;LabelField:dateselect.
If you leave out the dateselect column from the first DS then the dropdown
will default to 00:00:00 time, and looks awful.
Hope this helps,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||Ravi,
In addiditon to my last reply, if you want to make 'intelligent' parameters,
then your end date should be greater than your start date.
To achieve this, complete tasks as per my previous reply and add the
following;
Create a further DS called DSDates2.
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
HAVING (ModifiedDate > @.STARTDATE)
ORDER BY ModifiedDate
and change the source for the @.ENDDATE parameter to point to DSDates2.
You will then find the End Date drop down is disabled until the Start Date
is selected.
If you wish, you can set a default for the Start Date as being the first
date found in the table (Use Top 1 selected from DSDates) for extra
useability.
Hope this assists further,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||thanks logicalman,
will try that first thing on monday.
Ravi

DataReader and bad dates

Hello,

I am using a DataReader to read an ODBC Data Source.

Some of my dates are not valid. I would like to have the DataReader just treat the bad dates as Null. However I cannot seem to find a setting that will get me past the DataReader.

In SQL 2000 DTS I was able to use a script to skip the bad dates.

Please help because I don't want to have to run SQL 2000 DTS on SQL 2005.

Thanks,

Michael

You can send all rows that fail down the error output by setting ErrorRowDisposition=RD_RedirectRow for the output column in question. or, you can set ErrorRowDisposition=RD_IgnoreFailure and it will ignore those errors.

-Jamie

|||

Hi Michael,

If you right click the datareader src and choose Show Advanced Editor, then go to the Input and Output Properties tab, and expand the Output Columns collection, you can set the behaviour you want for error handling on a per-column basis. Click the column you want to ignore errors on, and change the "ErrorRowDisposition" property to "RD_IgnoreFailure". I think this will give you the result you are looking for -- NULL for columns that were invalid.

Let me know if this does not solve your issue.

Thanks
Mark