Solved

truncating a datetime in sql

Posted on 2014-07-24
10
226 Views
Last Modified: 2014-07-24
I have this query below that pulls the number of files imported by folder by date. The problem is the date is in this format 2014-06-18 02:36:38.000. So i am getting multiple results for the same day because the times are different.  I need them grouped by the date not time. How do i truncate "importDate" to yyyy-mm-dd.  I think that will group them correctly.

Select a.ImportDate, count(FileID) as Count, b.pipelineId from cnc_files a (nolock)
join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
group by a.importDate, b.PipelineID,  b.PipeLineID
order by ImportDate asc

Thank you for the help
0
Comment
Question by:jacobJL
[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
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40217034
>How do i truncate "importDate" to yyyy-mm-dd.
To transform it to a date datatype..
CAST(importDate as date) 

Open in new window

... or to transform it to a string formatted as YYYY-MM-DD*...
SELECT convert(varchar, CAST(importDate as date) ,120)

Open in new window

* Source:  SQL expert PortletPaul's article on SQL Server date styles
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40217038
try this:

Select CONVERT(char(10), a.ImportDate(),126), count(FileID) as Count, b.pipelineId from cnc_files a (nolock) 
 join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
 group by a.importDate, b.PipelineID,  b.PipeLineID
 order by ImportDate asc

Open in new window

0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 100 total points
ID: 40217039
group by CONVERT(nvarchar(10), a.importDate, 120)

Open in new window

0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 100 total points
ID: 40217057
Sorry, you may probably have figured this slight mistake of leaving () within date:

Select CONVERT(char(10), a.ImportDate,126) as ImportDate, count(FileID) as Count, b.pipelineId from cnc_files a (nolock) 
 join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
 group by a.importDate, b.PipelineID,  b.PipeLineID
 order by ImportDate asc

Open in new window

0
 
LVL 21

Assisted Solution

by:Tapan Pattanaik
Tapan Pattanaik earned 50 total points
ID: 40217098
Hi  jacobJL ,
Select CONVERT(VARCHAR(10), a.ImportDate, 120) as ImportDate , count(FileID) as Count, b.pipelineId from cnc_files a (nolock) 
 join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
 group by CONVERT(VARCHAR(10), a.ImportDate, 120), b.PipelineID,  b.PipeLineID
 order by ImportDate asc

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40217122
>> How do i truncate "importDate" to yyyy-mm-dd.  I think that will group them correctly. <<

1) Converting to char will be slow compared to the alternate method below.  And any subsequent date functions would require another conversion back to date.
2) If you do ever have to use varchar, use 'YYYYMMDD' during processing, because SQL always interprets that format correctly, whereas yyyy-??-?? depends on date settings.

To strip the time from a date efficiently, do this:
DATEADD(DAY, DATEDIFF(DAY, 0, <your_date>), 0)
[For example, run:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
]

If you need to format the output for display purposes, do it after the main query processing.


 Select dateadd(day, datediff(day, 0, a.ImportDate), 0) AS importDay, count(FileID) as Count, b.pipelineId from cnc_files a (nolock)
 join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
 group by a.importDate, b.PipelineID,  b.PipeLineID
 order by dateadd(day, datediff(day, 0, a.ImportDate), 0) asc


select convert(varchar(10), importDay, 120) as Import_Day, Count, pipelineId
from (
 Select dateadd(day, datediff(day, 0, a.ImportDate), 0) AS importDay, count(FileID) as Count, b.pipelineId from cnc_files a (nolock)
 join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
 group by a.importDate, b.PipelineID,  b.PipeLineID
) as subquery1
 order by ImportDay asc
0
 

Expert Comment

by:lrbrister
ID: 40217429
In SQL I've set up a function that handles quite a few formatting issues.
CREATE FUNCTION [dbo].[FormatDateTime] ( 
    @dt DATETIME, 
    @format VARCHAR(16) 
) 
RETURNS VARCHAR(64) 
AS 
BEGIN 
    DECLARE @dtVC VARCHAR(64) 
    SELECT @dtVC = CASE @format 
 
    WHEN 'LONGDATE' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
 
    WHEN 'LONGDATEANDTIME' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @dt, 112)), 22), 11) 
 
    WHEN 'SHORTDATE' THEN 
 
        LEFT(CONVERT(CHAR(19), @dt, 0), 11) 
 
    WHEN 'SHORTDATEANDTIME' THEN 
 
        REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
            'AM', ' AM'), 'PM', ' PM') 
 
    WHEN 'UNIXTIMESTAMP' THEN 
 
        CAST(DATEDIFF(SECOND, '19700101', @dt) 
        AS VARCHAR(64)) 
 
    WHEN 'YYYYMMDD' THEN 
 
        CONVERT(CHAR(8), @dt, 112) 
 
    WHEN 'YYYY-MM-DD' THEN 
 
        CONVERT(CHAR(10), @dt, 23) 
 
    WHEN 'YYMMDD' THEN 
 
        CONVERT(VARCHAR(8), @dt, 12) 
 
    WHEN 'YY-MM-DD' THEN 
 
        STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 
        5, 0, '-'), 3, 0, '-') 
 
    WHEN 'MMDDYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
 
    WHEN 'MM-DD-YY' THEN 
 
        CONVERT(CHAR(8), @dt, 10) 
 
    WHEN 'MM/DD/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 1) 
 
    WHEN 'MM/DD/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 101) 
 
    WHEN 'DDMMYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
 
    WHEN 'DD-MM-YY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
 
    WHEN 'DD/MM/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 3) 
 
    WHEN 'DD/MM/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 103) 
 
    WHEN 'HH:MM:SS 24' THEN 
 
        CONVERT(CHAR(8), @dt, 8) 
 
    WHEN 'HH:MM 24' THEN 
 
        LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 
 
    WHEN 'HH:MM:SS 12' THEN 
 
        LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
 
    WHEN 'HH:MM 12' THEN 
 
        LTRIM(SUBSTRING(CONVERT( 
        VARCHAR(20), @dt, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 
 
    ELSE 
 
        'Invalid format specified' 
 
    END 
    RETURN @dtVC 
END

Open in new window


To use it
select dbo.FormatDateTime(getdate(), 'SHORTDATEANDTIME'
0
 

Author Comment

by:jacobJL
ID: 40217521
Wow thanks everyone, i don't even know who to give points too because they all work.
The final output i went with was
Select cast(a.ImportDate as date), count(FileID) as Count, b.pipelineId from cnc_files a (nolock)
join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
group by cast(a.ImportDate as date), b.PipelineID,  b.PipeLineID
order by cast(a.ImportDate as date) desc

but this worked all the same. Truthfully i don't know what the differences is or if one of the other is the better option. Jim was the first to answer so i feel as i should give him majority of the points, but it wasn't till i read lee's that i figured it out.  

Select CONVERT(char(10), a.ImportDate,126) as ImportDate, count(FileID) as Count, b.pipelineId from cnc_files a (nolock)
 join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
 group by CONVERT(char(10), a.ImportDate,126), b.PipelineID,  b.PipeLineID
 order by CONVERT(char(10), a.ImportDate,126) desc
0
 

Author Closing Comment

by:jacobJL
ID: 40217532
Sorry for the 2 I didn't give points. Scott it wasn't for display purpose so when i ran yours it pulled the same data that i was originally pulling. most likely because of the group by clause.

All solutions were great. Thanks everyone for the help
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40217540
Yes, the GROUP BY obviously needs the same conversion as the SELECT column:

Select dateadd(day, datediff(day, 0, a.ImportDate), 0) AS importDay, count(FileID) as Count, b.pipelineId from cnc_files a (nolock)
  join CNC_Pipeline b on a.PipelineID=b.Pipelineid  
  group by dateadd(day, datediff(day, 0, a.ImportDate), 0), b.PipelineID,  b.PipeLineID
  order by dateadd(day, datediff(day, 0, a.ImportDate), 0) asc
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

724 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