SQL Syntax to split Date and Time

mahmed1 · Oct 19, 2016

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

Micron · Oct 22, 2016

I know of no way you can remove seconds from time values that are stored in table fields. Just because you don't show seconds does not mean they are not there. Dates and times are stored as numbers. The fact that you can display them in various ways does not change this fact. This 42665 is today's date. This 42665.4888541667 is today's date with the time at the moment I asked for it. I think you can see that you cannot remove the seconds part of this. You might want to review what's going on with time and date formats since I think you're missing my points in my first post. This is old, but probably good enough for illustration.

I'm at a disadvantage in that I haven't read the prior 47 posts, simply wanting to comment on grouping over time. Thus I could be repeating others by saying there might be a sql solution if you used the DatePart function and had a table of hours (08:00, 09:00 etc.; not sure which format to suggest). In pseudo syntax, WHERE DatePart("n","[YourDataTimeField]")> -1 AND DatePart("n","[YourDataTimeField]"< 60) to get minutes from 0 to 59. Somehow, you'd have to relate the hour portion of your data to your hours table, otherwise you'll get all records where the data time component matches a given minute, regardless of the hour or date. I think the DatePart function could compare the DatePart of your data to the hours table and give you a record where the hour data matches each DatePart hour. Hope my rambling makes sense.

xenou · Oct 24, 2016

This is on the right track finally. Notice that you have stopped asking for seconds to be displayed! Even minutes are not displayed, except the "bucket" 8:00 to 8:30 which is the grouping for the half hour period.

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

mahmed1 · Oct 24, 2016

Hi - yes thats correct - how can i achieve this Xenou?

thank you

xenou · Oct 24, 2016

Offhand, not sure. There has to be a way to manipulate your times directly to create the buckets but its probably a little tricky. Rounding issues might need careful attention here. Also I don't know Oracle so even if I could do this in Access SQL it would probably be different for Oracle SQL.

If you can split the time part out you can create a lookup table for the "interval" and get it that way. I.e., if

Code:

... blah blah blah ...
    JOIN My_Interval_Lookup_Table 
    ON TheTime >= TimeStart 
    AND TheTime < TimeEnd

using some kind of table such as:
---------------------------------------------------------
|   TimeStart |     TimeEnd | TimeInterval              |
---------------------------------------------------------
| 12:00:00 AM | 12:30:00 AM | 12:00:00 AM - 12:30:00 AM |
| 12:30:00 AM |  1:00:00 AM | 12:30:00 AM - 01:00:00 AM |
|  1:00:00 AM |  1:30:00 AM | 01:00:00 AM - 01:30:00 AM |
|  1:30:00 AM |  2:00:00 AM | 01:30:00 AM - 02:00:00 AM |
|  2:00:00 AM |  2:30:00 AM | 02:00:00 AM - 02:30:00 AM |
|  2:30:00 AM |  3:00:00 AM | 02:30:00 AM - 03:00:00 AM |
|  3:00:00 AM |  3:30:00 AM | 03:00:00 AM - 03:30:00 AM |
|  3:30:00 AM |  4:00:00 AM | 03:30:00 AM - 04:00:00 AM |
|  4:00:00 AM |  4:30:00 AM | 04:00:00 AM - 04:30:00 AM |
|  4:30:00 AM |  5:00:00 AM | 04:30:00 AM - 05:00:00 AM |
|  5:00:00 AM |  5:30:00 AM | 05:00:00 AM - 05:30:00 AM |
|  5:30:00 AM |  6:00:00 AM | 05:30:00 AM - 06:00:00 AM |
|  6:00:00 AM |  6:30:00 AM | 06:00:00 AM - 06:30:00 AM |
|  6:30:00 AM |  7:00:00 AM | 06:30:00 AM - 07:00:00 AM |
|  7:00:00 AM |  7:30:00 AM | 07:00:00 AM - 07:30:00 AM |
|  7:30:00 AM |  8:00:00 AM | 07:30:00 AM - 08:00:00 AM |
|  8:00:00 AM |  8:30:00 AM | 08:00:00 AM - 08:30:00 AM |
|  8:30:00 AM |  9:00:00 AM | 08:30:00 AM - 09:00:00 AM |
|  9:00:00 AM |  9:30:00 AM | 09:00:00 AM - 09:30:00 AM |
|  9:30:00 AM | 10:00:00 AM | 09:30:00 AM - 10:00:00 AM |
| 10:00:00 AM | 10:30:00 AM | 10:00:00 AM - 10:30:00 AM |
| 10:30:00 AM | 11:00:00 AM | 10:30:00 AM - 11:00:00 AM |
| 11:00:00 AM | 11:30:00 AM | 11:00:00 AM - 11:30:00 AM |
| 11:30:00 AM | 12:00:00 PM | 11:30:00 AM - 12:00:00 PM |
| 12:00:00 PM | 12:30:00 PM | 12:00:00 PM - 12:30:00 PM |
| 12:30:00 PM |  1:00:00 PM | 12:30:00 PM - 01:00:00 PM |
|  1:00:00 PM |  1:30:00 PM | 01:00:00 PM - 01:30:00 PM |
|  1:30:00 PM |  2:00:00 PM | 01:30:00 PM - 02:00:00 PM |
|  2:00:00 PM |  2:30:00 PM | 02:00:00 PM - 02:30:00 PM |
|  2:30:00 PM |  3:00:00 PM | 02:30:00 PM - 03:00:00 PM |
|  3:00:00 PM |  3:30:00 PM | 03:00:00 PM - 03:30:00 PM |
|  3:30:00 PM |  4:00:00 PM | 03:30:00 PM - 04:00:00 PM |
|  4:00:00 PM |  4:30:00 PM | 04:00:00 PM - 04:30:00 PM |
|  4:30:00 PM |  5:00:00 PM | 04:30:00 PM - 05:00:00 PM |
|  5:00:00 PM |  5:30:00 PM | 05:00:00 PM - 05:30:00 PM |
|  5:30:00 PM |  6:00:00 PM | 05:30:00 PM - 06:00:00 PM |
|  6:00:00 PM |  6:30:00 PM | 06:00:00 PM - 06:30:00 PM |
|  6:30:00 PM |  7:00:00 PM | 06:30:00 PM - 07:00:00 PM |
|  7:00:00 PM |  7:30:00 PM | 07:00:00 PM - 07:30:00 PM |
|  7:30:00 PM |  8:00:00 PM | 07:30:00 PM - 08:00:00 PM |
|  8:00:00 PM |  8:30:00 PM | 08:00:00 PM - 08:30:00 PM |
|  8:30:00 PM |  9:00:00 PM | 08:30:00 PM - 09:00:00 PM |
|  9:00:00 PM |  9:30:00 PM | 09:00:00 PM - 09:30:00 PM |
|  9:30:00 PM | 10:00:00 PM | 09:30:00 PM - 10:00:00 PM |
| 10:00:00 PM | 10:30:00 PM | 10:00:00 PM - 10:30:00 PM |
| 10:30:00 PM | 11:00:00 PM | 10:30:00 PM - 11:00:00 PM |
| 11:00:00 PM | 11:30:00 PM | 11:00:00 PM - 11:30:00 PM |
| 11:30:00 PM | 12:00:00 AM | 11:30:00 PM - 12:00:00 AM |
---------------------------------------------------------

mahmed1 · Oct 24, 2016

Hi Xenou

I am not sure how to remove the seconds or minutes

I was thinking if there was a replace/Trunc function to first strip off seconds and then do an if then to say if the minute is less than 30 then change to the hour prior..as an example say i had

08:24:45
08:24 (strip off seconds)
24 <30 then change to 08:00

08:30:45
08:30 (strip off seconds)
30 =30 or less than 00 then change to 08:30

And then what I can do is group it then or something like that

How do i create a table using excel

so create TempLookup

Create Table TempLookup
Workheets("TimeLookUp").range("A1:C200")

xenou · Oct 25, 2016

Offhand I don't know how to work with date/time effectively in Oracle so I'd have to do some research on that one. What have you discovered so far doing your own research on google?

Excel is not a database so it does not have any kind of create table statement. You can import table/query data into Excel from databases. Once in Excel you can call any set of data that has rows and columns a table.

xenou · Oct 25, 2016

Note: If you decide to work with date *strings* then be sure to format them in the proper way to allow for comparisons.

Example:

"03:25 PM" is less than "11:45 AM" -> TRUE (logically incorrect)
"15:25" is less than "11:45" -> FALSE (logically correct)

"05/31/2016" is less than "10/31/2015" -> TRUE (logically incorrect)
"2016-05-31" is less than "2015-06-30" -> FALSE (logically correct)

In general (For Dates stored as STRING values), times should be in 24 hour format, and dates should be in yyyymmdd format, and in combination, you can have date/times in yyyymmdd hh:mm:ss format. When you do this, you can use comparisons such as Less Than, Greater Than, and Equals.

Naturally, when you use dates as true dates (which is most systems are numeric values), then you can do comparisons on the dates directly regardless of format, as long as the actual values being compared are still truly datetime serials.

Mick Peters · Oct 25, 2016

Ok So I am not sure if I am missing something here but this works in Oracle SQL. It is correct you cannot group by an alias but you can name the alias again and group by that. However Xenou is correct in that if you group by any field with seconds in it then you are affectively splitting the group into such small pieces that the group is ineffective.
Try this
SELECT
TRUNC(End_tran_date) as "MY Date",
TO_CHAR(End_tran_date,'HH24:MI') As "My TIME",
TO_CHAR(End_tran_date,'HH24') As "Hour"
FROM Table_1 WHERE TRUNC(End_tran_date)Between TO_DATE('12/09/2016','DD/MM/YYYY')and to_date
('01/10/2016','DD/MM/YYYY')AND TO_CHAR(End_tran_date,'HH24:MI:SS')BETWEEN'02:00:00'AND'07:00:00'
Group By
TRUNC(End_tran_date),
TO_CHAR(End_tran_date,'HH24:MI'),
TO_CHAR(End_tran_date,'HH24')
Order by TRUNC(End_tran_date), TO_CHAR(End_tran_date,'HH24');

And you should have a result like,
MY Date My TIME Hour
12/09/2016 04:05 04
12/09/2016 05:50 05
12/09/2016 05:49 05
12/09/2016 05:53 05
12/09/2016 05:55 05
12/09/2016 05:48 05
12/09/2016 05:57 05
12/09/2016 05:56 05
12/09/2016 06:24 06
12/09/2016 06:18 06
12/09/2016 06:30 06
I hope this helps,
Mick.

Mick Peters · Oct 26, 2016

Hi I am not an Oracle expert but have found and modified this that might be closer to what you are looking for. It uses Case statements to divide the hour into 1/2 hour segments. If you only want 08:00 until 00:00 you can comment out or delete the other lines. This will need a little more to tidy it up and make it fit what you are after but hopefully it will give you an idea how to split those times and dates.

Code:

 SELECT End_Tran_Time AS order_date,
         SUM ( (CASE WHEN Hour = '18'and MIN < '30' THEN 1 ELSE 0 END)) AS "18",
         SUM ( (CASE WHEN Hour = '18'and MIN > '31' THEN 1 ELSE 0 END)) AS "18.5",
         SUM ( (CASE WHEN Hour = '19'and MIN < '30' THEN 1 ELSE 0 END)) AS "19",
         SUM ( (CASE WHEN Hour = '19'and MIN > '31' THEN 1 ELSE 0 END)) AS "19.5",
         SUM ( (CASE WHEN Hour = '20'and MIN < '30' THEN 1 ELSE 0 END)) AS "20",
         SUM ( (CASE WHEN Hour = '20'and MIN > '31' THEN 1 ELSE 0 END)) AS "20.5",
         SUM ( (CASE WHEN Hour = '21'and MIN < '30' THEN 1 ELSE 0 END)) AS "21",
         SUM ( (CASE WHEN Hour = '21'and MIN > '31' THEN 1 ELSE 0 END)) AS "21.5",
         SUM ( (CASE WHEN Hour = '22'and MIN < '30' THEN 1 ELSE 0 END)) AS "22",
         SUM ( (CASE WHEN Hour = '22'and MIN > '31' THEN 1 ELSE 0 END)) AS "22.5",
         SUM ( (CASE WHEN Hour = '23'and MIN < '30' THEN 1 ELSE 0 END)) AS "23",
         SUM ( (CASE WHEN Hour = '23'and MIN > '31' THEN 1 ELSE 0 END)) AS "23.5",
         SUM ( (CASE WHEN Hour = '00'and MIN < '30' THEN 1 ELSE 0 END)) AS "00",
         SUM ( (CASE WHEN Hour = '00'and MIN > '31' THEN 1 ELSE 0 END)) AS "00.5",
         SUM ( (CASE WHEN Hour = '01'and MIN < '30' THEN 1 ELSE 0 END)) AS "01",
         SUM ( (CASE WHEN Hour = '01'and MIN > '31' THEN 1 ELSE 0 END)) AS "01.5",
         SUM ( (CASE WHEN Hour = '02'and MIN < '30' THEN 1 ELSE 0 END)) AS "02",
         SUM ( (CASE WHEN Hour = '02'and MIN > '31' THEN 1 ELSE 0 END)) AS "02.5",
         SUM ( (CASE WHEN Hour = '03'and MIN < '30' THEN 1 ELSE 0 END)) AS "03",
         SUM ( (CASE WHEN Hour = '03'and MIN > '31' THEN 1 ELSE 0 END)) AS "03.5",
         SUM ( (CASE WHEN Hour = '04'and MIN < '30' THEN 1 ELSE 0 END)) AS "04",
         SUM ( (CASE WHEN Hour = '04'and MIN > '31' THEN 1 ELSE 0 END)) AS "04.5",
         SUM ( (CASE WHEN Hour = '05'and MIN < '30' THEN 1 ELSE 0 END)) AS "05",
         SUM ( (CASE WHEN Hour = '05'and MIN > '31' THEN 1 ELSE 0 END)) AS "05.5",
         SUM ( (CASE WHEN Hour = '06'and MIN < '30' THEN 1 ELSE 0 END)) AS "06",
         SUM ( (CASE WHEN Hour = '06'and MIN > '31' THEN 1 ELSE 0 END)) AS "06.5",
         SUM ( (CASE WHEN Hour = '07'and MIN < '30' THEN 1 ELSE 0 END)) AS "07",
         SUM ( (CASE WHEN Hour = '07'and MIN > '31' THEN 1 ELSE 0 END)) AS "07.5",
         SUM ( (CASE WHEN Hour = '08'and MIN < '30' THEN 1 ELSE 0 END)) AS "08",
         SUM ( (CASE WHEN Hour = '08'and MIN > '31' THEN 1 ELSE 0 END)) AS "08.5",
         SUM ( (CASE WHEN Hour = '09'and MIN < '30' THEN 1 ELSE 0 END)) AS "09",
         SUM ( (CASE WHEN Hour = '09'and MIN > '31' THEN 1 ELSE 0 END)) AS "09.5",
         SUM ( (CASE WHEN Hour = '10'and MIN < '30' THEN 1 ELSE 0 END)) AS "10",
         SUM ( (CASE WHEN Hour = '10'and MIN > '31' THEN 1 ELSE 0 END)) AS "10.5",
         SUM ( (CASE WHEN Hour = '11'and MIN < '30' THEN 1 ELSE 0 END)) AS "11",
         SUM ( (CASE WHEN Hour = '11'and MIN > '31' THEN 1 ELSE 0 END)) AS "11.5",
         SUM ( (CASE WHEN Hour = '12'and MIN < '30' THEN 1 ELSE 0 END)) AS "12",
         SUM ( (CASE WHEN Hour = '12'and MIN > '31' THEN 1 ELSE 0 END)) AS "12.5",
         SUM ( (CASE WHEN Hour = '13'and MIN < '30' THEN 1 ELSE 0 END)) AS "13",
         SUM ( (CASE WHEN Hour = '13'and MIN > '31' THEN 1 ELSE 0 END)) AS "13.5",
         SUM ( (CASE WHEN Hour = '14'and MIN < '30' THEN 1 ELSE 0 END)) AS "14",
         SUM ( (CASE WHEN Hour = '14'and MIN > '31' THEN 1 ELSE 0 END)) AS "14.5",
         SUM ( (CASE WHEN Hour = '15'and MIN < '30' THEN 1 ELSE 0 END)) AS "15",
         SUM ( (CASE WHEN Hour = '15'and MIN > '31' THEN 1 ELSE 0 END)) AS "15.5",
         SUM ( (CASE WHEN Hour = '16'and MIN < '30' THEN 1 ELSE 0 END)) AS "16",
         SUM ( (CASE WHEN Hour = '16'and MIN > '31' THEN 1 ELSE 0 END)) AS "16.5",
         SUM ( (CASE WHEN Hour = '17'and MIN < '30' THEN 1 ELSE 0 END)) AS "17",
         SUM ( (CASE WHEN Hour = '17'and MIN > '31' THEN 1 ELSE 0 END)) AS "17.5"
    FROM
   ( SELECT CASE
                    WHEN TO_CHAR (End_Tran_Time, 'HH24') BETWEEN '00' AND '05'
                    THEN
                       TRUNC (End_Tran_Time - 1)
                    ELSE
                       TRUNC (End_Tran_Time)
                 END
                    AS End_Tran_Time,
                 TO_CHAR (End_Tran_Time, 'HH24') AS Hour,
                 TO_CHAR(End_Tran_Time,'MI') As Min,
                 Order_number
                FROM Table_1
                Where Order_number Like '%1234%'
                )   
GROUP BY End_Tran_Time
order by order_date desc nulls last;

I hope this helps
Mick.

mahmed1 · Oct 28, 2016

Thank You all - My manager is a right PITA....What he is now saying that he does not care about the times - he is interested in how many came in that day..Now this SQL i had works fine however i dont need to group by any time but the date needs to be there in the column ie...

So this SQL..Gives me the required output however as you guys have mentioned already, the TS_MOVEMENT field had date and time in there..Now i know have to include this field in the group by if i use this in the SELECT statement

My question is i want to pull back all the information grouped the way it is..The problem i have is that the TS_MOVEMENT will have date and time now when grouping - this will have 2 rows

20/10/2016 13:45:45 COMPLAINTS TEAM WORK1 2
20/10/2016 13:46:45 COMPLAINTS TEAM WORK1 2

I will need it as

20/10/2016 COMPLAINTS TEAM WORK1 4

Basically i need to group the way i did but dont need the time part however i do need the date as a field so when exported to Excel, I can apply filters on the date part and the in SQL i wont get 2 different results for different times in the day..

My aim is to get all the different work that came in for a team on the date range so technically when group, I should only get 1 work Item for each team on a certain date

Code:

[COLOR=#333333]strSQL = "SELECT TS_MOVEMENT," & _[/COLOR]
[COLOR=#333333]" NM_DEPARTMENT," & _[/COLOR]
[COLOR=#333333]" NM_ORGANISATION," & _[/COLOR]
[COLOR=#333333]" TP_ACTIVITY," & _[/COLOR]
[COLOR=#333333]" DS_ACT_TYPE," & _[/COLOR]
[COLOR=#333333]" ACTION," & _[/COLOR]
[COLOR=#333333]" TEAM_FROM," & _[/COLOR]
[COLOR=#333333]" HH_NHH," & _[/COLOR]
[COLOR=#333333]" COUNT(DS_ACT_TYPE) " & _[/COLOR]
[COLOR=#333333]" FROM BTAYLO2.BI_INCOMING_AQS_DETAIL" & _[/COLOR]
[COLOR=#333333]" WHERE TS_MOVEMENT>= To_Date('" & StartDate & "','DD/MM/YYYY'')" & _[/COLOR]
[COLOR=#333333]" AND TS_MOVEMENT<= To_Date('" & EndDate & "','DD/MM/YYYY')" & _[/COLOR]
[COLOR=#333333]" GROUP BY NM_DEPARTMENT," & _[/COLOR]
[COLOR=#333333]" NM_ORGANISATION," & _[/COLOR]
[COLOR=#333333]" TS_MOVEMENT," & _[/COLOR]
[COLOR=#333333]" TP_ACTIVITY," & _[/COLOR]
[COLOR=#333333]" DS_ACT_TYPE," & _[/COLOR]
[COLOR=#333333]" ACTION," & _[/COLOR]
[COLOR=#333333]" HH_NHH," & _[/COLOR]
[COLOR=#333333]" DS_ACT_TYPE," & _[/COLOR]
[COLOR=#333333]" TEAM_FROM " & _[/COLOR]
[COLOR=#333333]" ORDER BY NM_DEPARTMENT ASC," & _[/COLOR]
[COLOR=#333333]" TS_MOVEMENT ASC," & _[/COLOR]
[COLOR=#333333]" NM_ORGANISATION ASC"[/COLOR]

SQL Syntax to split Date and Time

mahmed1

Well-known Member

Micron

Well-known Member

Excel Facts

xenou

MrExcel MVP

mahmed1

Well-known Member

xenou

MrExcel MVP

mahmed1

Well-known Member

xenou

MrExcel MVP

xenou

MrExcel MVP

Mick Peters

Board Regular

Mick Peters

Board Regular

mahmed1

Well-known Member

Similar threads

Forum statistics

Share this page

SQL Syntax to split Date and Time

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock