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]

What does this sql look like?
have you tried running it directly in Oracle?

You don't need to worry about the data types as long as you get good sql from your variables for start date etc. In any case, they should be either date or string variables. Seems like they are strings since you are concatenating them in your sql statement to other strings (without errors (?))

Found the error - it was the extra comma before the order by

1 problem I am having - some of the data retrieved can go quite further back so and the system can freeze so I added DoEvents just before the Rs.open however when I add this - I can see the screen updating even though its set to false

How can I add this without seeing the screen update?

 

[xenou]

I don't think you want to do anything with DoEvents here. Where did you get that idea from?

 

[mahmed1]

Hi Xenou- I could be pulling back data with 600,000 rows and without DoEvents- the screen kept showing Not Responding and goes all White (using excel 2013) so I thought Id add DoEvents to get round this

Hopefully you can advise better

and going back to your earlier post, Your 100% right about grouping the data with seconds is a bad idea - how can i amend the sql to group by every half hour instead excluding the seconds?

 

[xenou]

Easy: don't include seconds in your group by.

There's nothing you can do while your query is running anyway so may as well let the screen go white until if finishes - screen white or not you'll be waiting for the query to finish before Excel can do anything else.

 

[mahmed1]

Thank you - how do I eliminate the seconds ?

i want the data in the excel cell to show the full time and seconds but only group by date and time for every half hour period so 08:00:00 to 08:30:00 would group everything including 08:00:00 but before not including 08:30:00

Im all new to this

can you explain why To_Date is used with dates and To_Char is used for times?

 

[xenou]

You can't show seconds and also have groups. By definition, a group is a count or sum of all the records in the group - *as one record* - so each group collapses to a single row. For instance, the count of the records within each half hour interval. If you showed all the records with seconds, they aren't a group anymore.

You can read up on oracle to_date and to_char by googling those keywords, including oracle in your search term. Why they are being used in your particular query I don't really know. Please post the relevant part of the SQL when you ask a question.

 

[mahmed1]

So sorry and thank you

ok so I understand grouping - so going back to the grouping without seconds what do i need to amend/add to group without the seconds?

the to_date part to HH24:MI or Something in the Group By or something in the SELECT Query?

 

[xenou]

It depends. You can get by with just a WHERE criteria if all the data is in one group. Otherwise you must manipulate the date/time fields to get the groups you want.

 

[mahmed1]

Im so sorry but im not sure what you mean hun, can you give us an example please..

 

[xenou]

The simplest case if you have one group:

Select 
    Something, Count(something) 
From 
    Table 
Where 
    Date >= '2016-01-01' and Date < '2016-01-02'
Group By Something

That would count all the something on the first day of January 2016.