asked on

casting or converting datetime to only date for selection in crystal reports

I have a long datetime field. I am attempting to convert it to only a date so the users can enter the parameters in crystal reports.

So far I have convert(char(10), datefield, 101) which gives me the date in string format.

Now I need to make it a date format for either a data range in crystal or even date fields in an excel query.

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itatahh

ASKER

When I do the following it displays only the date:

SELECT TOP 10 PERCENT CONVERT(datetime, CONVERT(char(10), care_date, 101), 101) AS care_date1, *
FROM invdet

When I insert it into my view it still gives me date and time:

SELECT TOP 100 PERCENT off_code, Inv_Payer_No, client_no, Client_Name, invoice_no,
convert(datetime, convert(char(10), Create_Date, 101), 101) AS Create_Date,
convert(datetime, convert(char(10), Care_Date, 101), 101) AS care_date,
skilldesc, quantity, bill_unit, bill_rate, bill_amt, out_amt AS Gross_amt, Adj_Amt
FROM dbo.[Revenue Report 5e]
WHERE (skilldesc IS NOT NULL) AND (Inv_Payer_No <> 'H006')
ORDER BY invoice_no, version

Any thoughts?

davbouchard

Try..

SELECT TOP 100 PERCENT off_code, Inv_Payer_No, client_no, Client_Name, invoice_no,
convert(char(10), Create_Date, 101) AS Create_Date,
convert(char(10), Care_Date, 101) AS care_date,
skilldesc, quantity, bill_unit, bill_rate, bill_amt, out_amt AS Gross_amt, Adj_Amt
FROM dbo.[Revenue Report 5e]
WHERE (skilldesc IS NOT NULL) AND (Inv_Payer_No <> 'H006')
ORDER BY invoice_no, version

itatahh

ASKER

I went ahead and added the select criteria back to the crystal reports file. It seems as if the CR sees it as a date but not the excel query, whcih I was using to examine how the data would look outside of SQL.

So, your original solution worked.

Thanks.