Solved

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

Posted on 2006-06-09
4
527 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:itatahh
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 50 total points
Comment Utility
CONVERT(datetime, CONVERT(varchar, datefield, 101), 101)
0
 

Author Comment

by:itatahh
Comment Utility

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?
0
 
LVL 6

Expert Comment

by:davbouchard
Comment Utility
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
0
 

Author Comment

by:itatahh
Comment Utility
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now