truncating a datetime in sql

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
jacobJLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerCommented:
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
Lee SavidgeCommented:
group by CONVERT(nvarchar(10), a.importDate, 120)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sammySeltzerCommented:
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
Tapan PattanaikSenior EngineerCommented:
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
Scott PletcherSenior DBACommented:
>> 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
Larry Bristersr. DeveloperCommented:
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
jacobJLAuthor Commented:
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
jacobJLAuthor Commented:
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
Scott PletcherSenior DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.