Improve company productivity with a Business Account.Sign Up

x
?
Solved

SELECT DATE AND TIME PROBLEM

Posted on 2013-06-15
18
Medium Priority
?
479 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 13

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
LVL 5

Accepted Solution

by:
DOSLover earned 2000 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 50

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 50

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

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 50

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 50

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

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.

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.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

579 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