asked on

Extract Date from a DATETIME data type in SQL 2005

I have a view I am creating and I want to group by an extracted date from a column that has a date and time. This is where I am....

SELECT
ClockCode AS MOnum, SUM(Actual_Hrs) AS Runtothours, TStamp
FROM
dbo.Raw_Booking
WHERE
(Actual_Hrs > 0) AND (ClockCode LIKE 'MO%')
AND (TStamp > CONVERT(DATETIME, '2013-01-01 00:00:00', 102))
GROUP BY
ClockCode, TStamp

so while I have it grouped by the TStamp column there is a record for each one based on the time... I am looking to get a total amount of run time for each order by date not date and time.

The TStamp column is formatted as:

2013-01-02 08:18:47.000

and its data type is datetime...
All I am finding my research doing is complicating it...
HELP Please!!

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

mebaby333

ASKER

SELECT ClockCode AS MOnum, SUM(Actual_Hrs) AS Runtothours, CAST(CONVERT(VARCHAR, TStamp, 101) AS datetime
) AS Time
FROM Raw_Booking
WHERE (Actual_Hrs > 0) AND (ClockCode LIKE 'MO%') AND (TStamp > CONVERT(DATETIME, '2013-01-01 00:00:00', 111))
GROUP BY ClockCode, CAST(CONVERT(VARCHAR, TStamp, 101) AS datetime)

Done :)

mebaby333

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for mebaby333's comment #a39648516

for the following reason:

I solved it

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

mebaby333

ASKER

BriCrowe could you possible suggest good options to improving in SQL? or is it simply doing it?

Brian Crowe

You can probably improve performance by adding an index to the Raw_Booking table. Run the query in SSMS with "Show Actual Execution Plan" enabled. The execution plan will include reliable suggestions for indexes that it believes will improve performance.