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!!
LVL 5
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.
0
 
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.

SELECT    
    DATEADD(DAY, DATEDIFF(DAY, 0, TStamp), 0) AS [Date],
   ClockCode AS MOnum,
   SUM(Actual_Hrs) AS Runtothours
FROM
    dbo.Raw_Booking
WHERE Actual_Hrs > 0
   AND ClockCode LIKE 'MO%'
   AND TStamp > '2013-01-01 00:00:00'
GROUP BY
   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
0
 
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 :)
0
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
0
 
mebaby333It AdminAuthor Commented:
BriCrowe could you possible suggest good options to improving in SQL? or is it simply doing it?
0
 
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.
0
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.