[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SELECT DATE AND TIME PROBLEM

Posted on 2013-06-15
18
Medium Priority
?
474 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
[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
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 49

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 49

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 49

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 49

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 49

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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