Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Question Unanswered: HELP with SQL Date Function

    Is there a Function that would just give me the DATE and not the DATE & TIME? I am trying to group by VRUServiceDate but since this is a smalldatetime data type, the grouping does not work by date because the time shows.


    Thank you.

    Code:
    SELECT 
       VRUServiceDate AS [Service Date],
       EmployeeID AS [Rep ID],
       SUM(VRUDriveMiles) AS [Miles Traveled]
    FROM WorkOrder
    WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
    GROUP BY VRUServiceDate,EmployeeID
    ORDER BY EmployeeID,VRUServiceDate

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would use the CONVERT function --

    SELECT
    CONVERT(CHAR(10),VRUServiceDate,126) AS [Service Date],
    EmployeeID AS [Rep ID],
    SUM(VRUDriveMiles) AS [Miles Traveled]
    FROM WorkOrder
    WHERE TagToPay=1 AND Paid=0 and vrudrivemiles <> 0
    GROUP BY CONVERT(CHAR(10),VRUServiceDate,126),EmployeeID
    ORDER BY EmployeeID,CONVERT(CHAR(10),VRUServiceDate,126)

    if the expression does not work in the ORDER BY, use the ordinal form instead -- ORDER BY 2,1

    rudy
    http://r937.com/

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Thumbs up

    It worked great but I had to change 126 to 101. Thanks!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •