?
Solved

truncating a datetime in sql

Posted on 2014-07-24
10
Medium Priority
?
230 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 1000 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 400 total points
ID: 40217039
group by CONVERT(nvarchar(10), a.importDate, 120)

Open in new window

0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 400 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 200 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

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

801 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