Solved

Extract Date from a DATETIME data type in SQL 2005

Posted on 2013-11-14
6
409 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
  • 3
  • 3
6 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 500 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now