none
how can i return date only without time

    Question

  • how can i return date only without time from this query in my report

    Code Snippet

    SELECT     company_id, seq_no, emp_no, absence_id, start_date, end_date, wage_code
    FROM         emp_absences
    WHERE     (company_id = @CompanyID) OR
                          (emp_no = @EmployeeID) OR
                          (start_date = @StartDate) OR
                          (end_date = @EndDate) OR
                          (wage_code = @WageCode)

     

     

     

    Sunday, February 24, 2008 6:50 AM

Answers

All replies

  • Not sure which part you need to convert to simply return the date, but in either case you can simply use the CAST or CONVERT function to put the date in the format that you are looking for depending on the format that you need --> CONVERT(varchar, @StartDate, 110).

     

    Refer to the SQL BOL for the different options: http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx.

    Sunday, February 24, 2008 11:34 AM
  • You can go thru rather complex gyrations on the query side, to break apart the date and reassemble it as a varchar field, i.e.:

     

    cast(cast(month(start_date) as varchar) + '/' + cast(day(start_date) as varchar) + '/' + cast(year(start_date) as varchar) as varchar)

     

    or, you can simply pull the date down in the query as-is, and format it when you go to display it on your report:

     

    =FormatDateTime(Fields!start_date.Value, 2)

     

    Personally, I use the latter technique.

    Sunday, February 24, 2008 11:37 AM
  • 2 Options

    either use trunc. See below

     

    SELECT     company_id, seq_no, emp_no, absence_id, trunc(start_date), trunc(end_date), wage_code
    FROM         emp_absences
    WHERE     (company_id = @CompanyID) OR
                          (emp_no = @EmployeeID) OR
                          (start_date = @StartDate) OR
                          (end_date = @EndDate) OR
                          (wage_code = @WageCode)

     

    or keep the times in the query and format the cell to only display date

     

    1. Right click on date textbox
    2. Select Properties
    3. Select the format tab
    4. Top left you will see format code click on the box with the three dots and select the date format that you want

    Hope this helps

    Mike

    Sunday, February 24, 2008 7:31 PM
  • Hi,

    You may use the Convert and Cast function: CAST and CONVERT (Transact-SQL)

    Monday, February 25, 2008 7:29 AM