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
Solved

Extract Date from a DATETIME data type in SQL 2005

Posted on 2013-11-14
6
414 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

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.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

856 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