Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Extract Date from a DATETIME data type in SQL 2005

Posted on 2013-11-14
6
Medium Priority
?
433 Views
Last Modified: 2013-11-14
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!!
0
Comment
Question by:mebaby333
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 2000 total points
ID: 39648453
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
 
LVL 5

Author Comment

by:mebaby333
ID: 39648516
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
 
LVL 5

Author Comment

by:mebaby333
ID: 39648988
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39648539
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
 
LVL 5

Author Comment

by:mebaby333
ID: 39648999
BriCrowe could you possible suggest good options to improving in SQL? or is it simply doing it?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39649033
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question