truncating a datetime in sql

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

Select allOpen in new window

>> 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

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

Select allOpen in new window

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

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

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

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