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

 

[mahmed1]

Thanks - How do I encorporate in my SQL string ?

The where clause will bring back all data from the date and time range specified

I guess i then need to group by the minutes but as you can see i have selected a few columns in my select statement so these fields will also need to be in the Group By

 

[mahmed1]

This is my SQL string Hun, what exactly am
i changing to group the dateandtime field by every 30 mins or an hour..


strSQL = "SELECT TS_MOVEMENT," & _
" NM_DEPARTMENT," & _
" NM_ORGANISATION," & _
" TP_ACTIVITY," & _
" DS_ACT_TYPE," & _
" ACTION," & _
" TEAM_FROM," & _
" HH_NHH," & _
" COUNT(DS_ACT_TYPE) " & _
" FROM BTAYLO2.BI_INCOMING_AQS_DETAIL" & _
" WHERE TS_MOVEMENT>= To_Date('" & StartDate & " " & StartTime & "','DD/MM/YYYY HH24:MI:SS')" & _
" AND TS_MOVEMENT<= To_Date('" & EndDate & " " & EndTime & "','DD/MM/YYYY HH24:MI:SS')" & _
" GROUP BY NM_DEPARTMENT," & _
" NM_ORGANISATION," & _
" TS_MOVEMENT," & _
" TP_ACTIVITY," & _
" DS_ACT_TYPE," & _
" ACTION," & _
" HH_NHH," & _
" DS_ACT_TYPE," & _
" TEAM_FROM " & _
" ORDER BY NM_DEPARTMENT ASC," & _
" TS_MOVEMENT ASC," & _
" NM_ORGANISATION ASC"

 

[xenou]

Exactly what groups do you want? You still have TS_MOVEMENT in your group by clause so as far as I can tell you haven't changed anything at all here.

Please remember that you cannot put a fields with seconds displayed in your group by and also have groups that don't include seconds.

 

[mahmed1]

I want to group the ts_movement by 30 mins or hourly..

So i guess i need to remove the seconds and then do a group by but i dont know how

What exactly am i meant to be changing in the group by?

The Ts_MOVEMENT column has date and times with seconds
I am using the to_date to get the data for a specific date and time range
I then want to exclude the seconds and do a count which i have done but group in 30 min slots or hour

so basically the SQL I have but group the TS_Movement by 30 mins or an Hour..im so sorry i dont know how else to explain

I thought i had to include all fields in the select statement in the group by?

so the count is on the DS_ACT_TYPE so all other fields need to be in group by

 

[mahmed1]

So results showing something like this...

20/10/2016 08:00 - 08:30 TEAM1 BILLING 50.....etc
20/10/2016 08:00 - 08:30 TEAM1 MANADJ 90.....etc

The SQL i have but then just splitting it by 30 min buckets or hour buckets

I need to bring back all the fields i have specified

 

[xenou]

please show how you would like to see the result of your query *exactly*.

 

[mahmed1]

Hi Xenou

I dont have access to a computer at present

The current SQL gives all the pieces of work that came in for that date and time

Now grouping currently with seconds makes the result set massive

All i need is to do the same thing but grouped by every half hour or hour

Something like my previous thread example

My aim is to pull back all the data for each department team and break down how many came in every hour

Now my SQL works fine but rather than it including the seconds ignore it so its more on an hour basis or 30 mins grouped

Il give a sample when i get home love but i guess i cant explain any better (sorry)

 

[mahmed1]

A quick sample of the top my head shows the current SQL gives this if i bought back data from 17/10/2016 from 08:00 to 12:00 (example)

Billing 18/10/2016 08:00 COMPLAINTS TEAM COMPLAINTS WORK COMPLAINTS CODE ASSIGNED FROM JAY 50 CAME IN
Billing 18/10/2016 08:00 COMPLAINTS TEAM QUEUE WORK QUEUE WORK CODE ASSIGNED FROM JAY 80 CAME IN
Billing 18/10/2016 08:01 PROJECT TEAM COMPLAINTS WORK COMPLAINTS CODE ASSIGNED FROM JAY 50 CAME IN

I guess what i want is the same as above but group eveything by 30 minute slots so i can basically see

for each department grouped by every hour
How much of each work
came in for that team and thay department

I guess i dont need assigned from

Is that still unclear

 

[Micron]

Forgive me if I don't read all prior 47 posts for this one, but I thought it might help if I could shed some light on why you can't group by a field that contains time. When grouping, all the records that belong to a group can be put into the same 'basket', but when time gets involved, there isn't ONE record from a field that has the same value as ANY other, so each time value (likely down to the second) becomes its own group. Even if you're not displaying seconds, they are part of the field's value.

The only way I could envision you being able to accomplish this is via code where you build a recordset by adding records to it IF the difference in time between one source record and another is n minutes (whatever that number is). This recordset would need some common identifier (based on what little I read here, perhaps that is the hour, such as 08:00). Once the difference exceeds your number, start the loop over again with the new identifier (09:00).

If there is a subquery solution whereby you get some Min and Max values of time, I would not be able to figure it out as subqueries usually make my head hurt!

 

[mahmed1]

Hi Can i not add another column removing the seconds leaving just the minutes and then grouping that column by every half?

My manager wants to know how many work we are getting in every 30mins or say an hour..

if I have data like this

BILLING 08/10/2016 08:00 Work1 50
BILLING 08/10/2016 08:00 Work1 60
BILLING 08/10/2016 08:10 Work1 40
BILLING 08/10/2016 08:29 Work2 100

By Grouping it i can condense it as currently the data returned back to Excel can be huge
So the results should be

BILLING 08/10/2016 08:00-08:30 Work1 150
BILLING 08/10/2016 08:00-08:30 Work2 100

It would be awesome if i can have the time in its own column