Solved

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

Posted on 2006-06-09
4
547 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Accepted Solution

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

Author Comment

by:itatahh
ID: 16872447

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
ID: 16872568
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
ID: 16873003
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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