• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

SELECT DATE AND TIME PROBLEM

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
emi_sastra
Asked:
emi_sastra
  • 8
  • 5
  • 2
  • +2
1 Solution
 
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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
 
DOSLoverCommented:
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
 
emi_sastraAuthor Commented:
Hi DOSLover,

I want to convert it to datetime after remove seconds,

How could I do it ?

Thank you.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DOSLoverCommented:
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
 
emi_sastraAuthor Commented:
Great.

It works.

Thank you very much for your help.
0
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
darn it...

please read my comments?
0
 
emi_sastraAuthor Commented:
Hi PortletPaul,

Let me try it.

Thank you.
0
 
emi_sastraAuthor Commented:
I have no idea, what is the different ?

Thank you.
0
 
PortletPaulfreelancerCommented:
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
 
emi_sastraAuthor Commented:
-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
 
PortletPaulfreelancerCommented:
>>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
 
ThomasianCommented:
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
 
emi_sastraAuthor Commented:
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
 
PortletPaulfreelancerCommented:
I'm fine - Thomasian's contributon is very neat

(why didn't I just use minutes? I need a break...)
0
 
emi_sastraAuthor Commented:
Hi  PortletPaul,

Ok.

Thank you.
0
 
ThomasianCommented:
Glad we could help. No need to reopen the question.
0
 
emi_sastraAuthor Commented:
Hi   Thomasian,

Ok.

Thank you.
0
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

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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