Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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
0
jacobJL
Asked:
jacobJL
  • 2
  • 2
  • 2
  • +4
4 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now