SQL Syntax to split Date and Time

[mahmed1]

Hiya guys,

Im hoping an SQL guru can help me


Im using Oracle.


I have a column with Date and Time in the database like
18/10/2016 08:00:36


How can i separate these into 2 columns and extract that and this SQL aint working because of the syntax - where am i going wrong?


Date Time
18/10/2016 08:00:36


I have used the to_date function to convert the string into dates but im struggling to put these in separate columns


SELECT USERID,
DATETIME,
To_date(DATETIME,'DD/MM/YYYY') as "MyDate",
To_char(DATETIME,'DD/MM/YYYY')
as "MyTime",
ACCOUNT,
COUNT(QUEUECOUNT)
FROM TABLE1
Where DATETIME >= To_date('18/10/2016 08:00:00,'DD/MM/YYYY HH24:MM:SS')
AND
DATETIME <= To_date('18/10/2016 23:59:59,'DD/MM/YYYY HH24:MM:SS')
GROUP BY
USERID,
DATETIME,
MyDate,
MyTime,
ACCOUNT,
Order By MyDate, MyTime

 

[xenou]

Naturally, the answer to your question is to use the date part of the datetime field for you grouping. Do not include times in the final grouped result as that is not possible when you group on the dates only.

 

[mahmed1]

Hi Xenou,

What do you mean by that buddy can i get an example, i am still learning

Are you saying i need to wrap this part of the group by

Ts_Movement
to
Something else ????

 

[xenou]

That is correct. You should have done this already when we were grouping before, since the problem from the start has been you cannot (reasonably) group by a field that has times with seconds in it. Isn't that what you did already?

 

[mahmed1]

Hi Buddy

At first i grouped by the date and time field

I then tried the trunc around the to_date function to return just the date part but i now based on what your saying i need to do it im the group by

what is the correct way and what syntax am i using on the group by?

 

[xenou]

This would be sample syntax (using Access SQL, which will not work in Oracle SQL, but at least the syntax is similar, if the exact date functions are different).

SELECT 
	COUNT(a) as Total, 
	b, 
	c,
	d,
	CDate(Format(myDate,"dd/mm/yyyy")) as DateOnly
FROM
        MyTable
GROUP BY
	b,
	c,
	d,
	CDate(Format(myDate,"dd/mm/yyyy")) as DateOnly

 

[mahmed1]

If i think im right, in Oracle - the To_Date just converts to a date therefore if say i had the date and time
28/10/2016 15:56:43 in the Ts_Movement Field and i wrapped the to_date without specifying the time
ie WHERE To_Date(Ts_Movement,'DD/MM/YYYY') this will still return
28/10/2016 15:56:43 but will be converted to a date datatype therefore the Time Element is still there (nothing changes other that converting to a date data type but the time element is still there)

However, If i apply the Trunc to it
ie TRUNC(TO_DATE(Ts_Movement,'DD/MM/YYYY'))
This converts it to a Date Data Type and removes the time Element)

If the above is right..im thinking do i need to apply the Trunc to all 3 parts
ie

SELECT TRUNC(TO_DATE(Ts_Movement,'DD/MM/YYYY'))
WHERE TRUNC(TO_DATE(Ts_Movement,'DD/MM/YYYY'))
GROUP BY TRUNC(TO_DATE(Ts_Movement,'DD/MM/YYYY'))

or is it not needed in all 3 parts?

 

[xenou]

It looks like it would be needed in the select clause (assuming you want the date displayed in your results), and in the group by.

Don't need it in where necessarily, unless you want to filter results to a certain range (such as where dates between 1/1/2016 and 12/31/2016). Even then not strictly necessary, actually, at least not the TRUNC part, since you can still test the dates directly for such boundaries).

 

[mahmed1]

Hi Xenou - i want to say thank you for everything..

Is this correct buddy..

if i am testing between date range say
Between 01/01/2016 and 28/10/2016, the reason why i wont need the trunc to take the time off is because the date will technically compare between 01/01/2016 00:00:00 and <= 28/10/2016 23:59:59

 

[xenou]

Maybe.

I would interpret BETWEEN 01/01/2016 and 28/10/2016
as the same as
>=01/01/2016 00:00:00 and <= 28/10/2016 00:00:00
because you haven't given any time information. But this is something you should verify for yourself because Oracle might not work that way. I usually don't use Between, so I'm not really the one to ask.

But of course you can write (without any concern about what it means)
>=01/01/2016 00:00:00 and <= 28/10/2016 23:59:59

or (my habit)
>=01/01/2016 and < 29/10/2016

In any case, Trunc is not necessary with WHERE - as long as you set up the where condition properly.

You can verify all of this by running some tests and seeing what happens.