Solved

SELECT DATE AND TIME PROBLEM

Posted on 2013-06-15
18
443 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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