Solved

SELECT DATE AND TIME PROBLEM

Posted on 2013-06-15
18
436 Views
Last Modified: 2013-06-16
Hi All,

I have table with datatype type datetime.

I want to select distinct just date and time without seconds (dd/MM/yyyy HH:MM).
The hour is from 00 to 23.

How could I do it ?

Thank you.
0
Comment
Question by:emi_sastra
  • 8
  • 5
  • 2
  • +2
18 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39250852
Use cast or convert to change the field to varchar or nvarchar, then use the LEFT function to only select the left 10 characters.
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39250853
There is no datetime style defined for that format. You can accomplish what you need as:
select distinct convert(varchar(10), yourDateTimeColum, 103) 
       + ' ' + convert(varchar(5),yourDateTimeColum, 108) 
   from yourTable;

Open in new window

This will return the value in varchar format.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39250861
Hi DOSLover,

I want to convert it to datetime after remove seconds,

How could I do it ?

Thank you.
0
 
LVL 5

Accepted Solution

by:
DOSLover earned 500 total points
ID: 39250875
Hi Emi_Sastra,
If you need it back into datettime format, it will put seconds and milliseconds (both zeros, 00.000):

select distinct CAST(
       convert(varchar(10), yourDateTimeColum, 101) + ' ' + convert(varchar(5), yourDateTimeColum, 108) as datetime)
from yourTable  ;

Open in new window

0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 39250876
Great.

It works.

Thank you very much for your help.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250877
Although you have simply asked for "select distinct" frequently this is really a pre-cursor to some more complex need. Using varchar representations of date/time information might appear to suit  - but it could also lead to other issues - particularly with ordering the information.

Below I have presented use of some compound date functions - and you might consider this "complex" (nb: I have presented with indents to help you read it) - however:
a. in large volumes, date functions will outperform date to varchar
b. by retaining the inherent date/time information you can sort correctly
c. if also needing date range filtering then retaining date/time may also be important
d. consider using group by as an alternative to "distinct" but this will depend on your overall needs.

compare the following with a varchar approach at: http://sqlfiddle.com/#!3/1ebee/8
look carefully '01 May' and '01 June' if you go there (as an example)

-- remain with date functions
-- faster
-- retain date order
select distinct
  dateadd(minute,datepart(minute,yourDateTimeColum),
          dateadd(hour,datepart(hour,yourDateTimeColum),
                  dateadd(day,0,datediff(day,0,yourDateTimeColum))
          )
  )
, 'using date functions, retain date ordering, with distinct'
from yourTable
order by 1
;

-- remain with date functions
-- faster
-- retain date order
select
  dateadd(minute,datepart(minute,yourDateTimeColum),
          dateadd(hour,datepart(hour,yourDateTimeColum),
                  dateadd(day,0,datediff(day,0,yourDateTimeColum))
          )
  )
, 'using date functions, retain date ordering, via group by'
from yourTable
group by
  dateadd(minute,datepart(minute,yourDateTimeColum),
          dateadd(hour,datepart(hour,yourDateTimeColum),
                  dateadd(day,0,datediff(day,0,yourDateTimeColum))
          )
  )
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250879
darn it...

please read my comments?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39250906
Hi PortletPaul,

Let me try it.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39250908
I have no idea, what is the different ?

Thank you.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250910
the date order is different (aside from the query syntax)
IF you need to retain date order, using varchar dd/mm/yyyy hh:mm, you will get strange results when ordered

varchar
01/05/2013
01/06/2013 -- << wrong
02/05/2013 -- << wrong
02/06/2013
...

datetime
01/05/2013
02/05/2013
01/06/2013
02/06/2013

you quite possibly have only asked 'part of' an overall need - I'm making you aware of possible issues that you might have downstream.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39250954
-IF you need to retain date order, using varchar dd/mm/yyyy hh:mm, you will get strange results when ordered
Ok. Thus I have to use dateadd to solve it ?

Thank you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39250995
>>Ok. Thus I have to use dateadd to solve it ?
I proposed a method that permits selection of 'distinct' date/time ignoring time units smaller than minute  - and which retains date/time sequence...

You accepted the varchar solution whilst I was preparing my comment
- not much more I can add really
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39251015
The accepted solution's query did convert it back to datetime so sorting would not be a problem. Though I suggest using CONVERT instead of CAST to avoid errors/incorrect result due to the database's DATEFORMAT settings.

On the other hand, I agree with PortletPaul that using date functions instead of manipulating varchars would perform better. You can remove the second part of a datetime field by simply applying DATEADD/DATEDIFF on a constant date.
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,0,yourDateTimeColum),0)

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39251179
Hi  PortletPaul and Thomasian,

Thank you very much for helping me.

Glad to hear your solution.

Should I open this question for distribute points  ?

Thank you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39251180
I'm fine - Thomasian's contributon is very neat

(why didn't I just use minutes? I need a break...)
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39251186
Hi  PortletPaul,

Ok.

Thank you.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39251245
Glad we could help. No need to reopen the question.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39251251
Hi   Thomasian,

Ok.

Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now