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

 

[Norie]

What's the name and data type of the field?

 

[mahmed1]

The name of the field is called DATETIME

Im not sure what datatype its using but know that everyone in the dept uses the to_date function converting the date string to date

 

[mahmed1]

Im all new to this

I was trying to adda column for date and another column for time (i tried to name the columns MyDate and MyTime

I wanted to Aggregate the queuecount column and then do a group by and then order by MyDate and then MyTime but i know im going wrong..

I asked 1 chap at work to help me and his response was "I aint gona show you but i can get the results for you"

I honestly hate it when people dont want to help to keep it all to themself

 

[Norie]

For the time have you tried using to_char with 'HH24:MI:SS' instead of 'DD/MM/YYYY'?

 

[mahmed1]

Hi

Sorry Yes I tried that (i typed that by hand hence)

I think its something to do with the group by, order by or the criteria when im retrieving the data for >= and <= the datetime field

 

[Norie]

In what you posted you aren't using 'HH24:MI:SS' anywhere.

 

[mahmed1]

Sorry - it really doesnt help me typing on my mobile by hand
really apologize

this is what ive got

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

 

[Norie]

How isn't that working?

Have you tried it without the WHERE, GROUP BY and ORDER BY clauses?

If that works try adding in each of those clauses on it's own, running the query, checking the results etc.

If they work on their own you can then try combining them.

Also, you might want to check what To_date('18/10/2016 08:00:00,'DD/MM/YYYY HH24:MI:SS') and To_date('18/10/2016 23:59:59,'DD/MM/YYYY HH24:MI:SS') are actually returning.

PS Are you sure the field name is DATETIME? I'm sure that's a reserved word in some versions of SQL.

 

[xenou]

grouping on a field that includes seconds is very suspicious - probably groups of only 1 record will results, which is no grouping at all. To Norie's point, start with a simpler query, then build from there. For, instance, start by getting just your datetime field. Then add the criteria for filtering dates. then add the split out by date and time. Finally add your count() field.