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....

    ClockCode AS MOnum, SUM(Actual_Hrs) AS Runtothours, TStamp
   (Actual_Hrs > 0) AND (ClockCode LIKE 'MO%')
   AND (TStamp > CONVERT(DATETIME, '2013-01-01 00:00:00', 102))
   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!!
mebaby333It AdminAsked:
Who is Participating?
Brian CroweConnect With a Mentor Database AdministratorCommented:
There is no need to do a conversion on TStamp in the WHERE clause since the datatype is datetime and there is an implicit conversion from '2013-01-01'.  There are several date formats that are automatically interpreted by a datetime datatype with yyyymmdd being universal across all locales.  You could also use YEAR(TStamp) >= 2013 in this case.

The DATEADD(DATEDIFF) method will run faster than the double conversion.  Converting to DATE type is even faster but would require SQL Server 2008 or later.
Brian CroweConnect With a Mentor Database AdministratorCommented:
First, keep in mind that a datetime datatype is not stored in any format.  It is a couple of integers representing days and milliseconds.  Any formatting is determined by your localization settings.

    DATEADD(DAY, DATEDIFF(DAY, 0, TStamp), 0) AS [Date],
   ClockCode AS MOnum,
   SUM(Actual_Hrs) AS Runtothours
WHERE Actual_Hrs > 0
   AND ClockCode LIKE 'MO%'
   AND TStamp > '2013-01-01 00:00:00'
   ClockCode, DATEADD(DAY, DATEDIFF(DAY, 0, TStamp), 0)

Note: There are more elegant ways to strip the time off a datetime in SQL Server versions after 2005
mebaby333It AdminAuthor Commented:
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 :)
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

mebaby333It AdminAuthor Commented:
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
mebaby333It AdminAuthor Commented:
BriCrowe could you possible suggest good options to improving in SQL? or is it simply doing it?
Brian CroweDatabase AdministratorCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.