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]

Thank You

Im away from work but i think the field is DATEANDTIME

At work, the results did return without using the to_date and to_char in the select query and without it in the group by

I then tried to add 2 columns in the select query to split the date and time
now because i used the count on the QUEUECOUNT Column, i thought i needed to add these fields in the group by clause

My aim is to be able to extract the data for a given time period

so if i wanted to see what came in from 18:00 Until Midnight then retrieve the data for that period
i wanted to export this to excel and apply a filter therefore wanted to split the date and time column in SQL before extracting the data and also sorting by the date and time first becore extracting - how would you do that?

ps some DATEANDTIME fields have just the DATE in there - if thatis the case, what function can i use to replace any DATEANDTIME field that has just dates to show the DATE AND TIME AS 00:00:00 ie
If the date just showed 18/10/2016 then id need it to show 18/10/2016 00:00:00

 

[mahmed1]

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.

- Hi Xenou - i wanted to show all the incoming count grouped date and then time ie 08:00:00 to 00:00:00 or anything that came after 8am and before 12 midnight so 23:59:59 because 00:00:00 will be the next day

 

[mahmed1]

From what i have read, I believe grouping by column alias is not aloud in oracle - not sure whats the best way to get round this

My goal is to Extract data for a specific date and time range

so if my manager asked me he wanted to see all incoming work the 18/10/2016 from 8pm to midnight (23:59:59)
I need to extract the data based on those conditions
I then want to be able to split the DATEANDTIME column into seperate columns in the SELECT statement
I have the aggregate Function to count the queue volume on QUEUECOUNT and then have inputted the rest of the columns in the group by clause

Now I guess you cant group the alias names so dont know how i could include the date and time split in the select statement

 

[xenou]

Can you find a simple starting place for your query that *does* work? Like just getting the base data you need? As far as doing it in Excel, that's another application. You would use functions. But its too confusing to try to answer your SQL and Excel questions at the same time. If you can get the base data you need and put it into Excel you can the work in Excel alone - using formulas or pivot tables, probably.

 

[mahmed1]

Can you find a simple starting place for your query that *does* work? Like just getting the base data you need? As far as doing it in Excel, that's another application. You would use functions. But its too confusing to try to answer your SQL and Excel questions at the same time. If you can get the base data you need and put it into Excel you can the work in Excel alone - using formulas or pivot tables, probably.

Sorry Xenou, my bad, I completely got the wrong field names

Now this SQL definitely works

I am trying to run the SQL statement from Excel VBA but there seems to be a syntax error

What I need is the start date, End Date, Start Time and End Time to replace the hard coded date and time

So this part should be concatenated with date and time but syntax is showing red

" 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') & _

Ps what datatype should StartDate, EndDate, StartTime and EndTime be? Variant, Date, Time or long?

Sub ADO_SQL()
Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim strDB As String
Dim strlogin As String
Dim strpass As String
Dim connection_string As String
Dim ws As Worksheet
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
Set ws = Worksheets("DATA DUMP")

  'Loads database login details
    strDB = "????"     '(Schema Details)
    strlogin = "????" '(MyLogin UserId)
    strpass = "????" '(MyPassword)

    '//Connection string  'Provider
    connection_string = "Provider=OraOLEDB.Oracle.1;" & "Password=" & strpass & "; User ID=" & strlogin & ";Data Source=" & strDB & "; Persist Security Info=True"

'    '//Open a connection with the connection string
    With cn
        .ConnectionString = connection_string
        .Open
    End With
    
'Date pickers and Combo box which holds date and times
    StartDate = IncomingFrm.DTPickerStart.Value
    EndDate = IncomingFrm.DTPickerEnd.Value
    StartTime = IncomingFrm.CboTimeStart.Value
    EndTime = IncomingFrm.CboTimeEnd.Value
    
           
        strSQL = "SELECT TS_MOVEMENT," & _
                    " NM_DEPARTMENT," & _
                    " NM_ORGANISATION," & _
                    " TP_ACTIVITY," & _
                    " DS_ACT_TYPE," & _
                    " ACTION," & _
                    " TEAM_FROM," & _
                    " HH_NHH," & _
                    " COUNT(DS_ACT_TYPE) as ""TOTAL INCOMING"""
                 " FROM BTAYLO2.BI_INCOMING_AQS_DETAIL" & _
                 " WHERE TS_MOVEMENT>= To_Date('18/10/2016 00:00:00','DD/MM/YYYY HH24:MI:SS') & _
                 " AND TS_MOVEMENT<= To_Date('18/10/2016 23:59:59','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"
 

'    With cmd
'        .ActiveConnection = cn
'        .CommandType = adCmdText
'        .CommandText = sSQL
'        .Execute
'    End With
    '//SQL Command to input data from recordeset
   
            
    'Runs SQL statement and stores in memory
     rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
    
     'Pastes SQL data which is in rs to Range ("A2") (Copy From recordset does not include Headers)
     ws.Range("A8").CopyFromRecordset rs
     
     'Copy Header Names from Recordset
     
     For i = 0 To rs.Fields.Count - 1
        ws.Cells(7, i + 1).Value = rs.Fields(i).Name
     Next i
    '//Close ADO objects
     rs.Close
     cn.Close

     '//Cleanup (optional)
     Set rs = Nothing
     Set cn = Nothing
     Set cmd = Nothing
     strSQL = ""
End Sub

 

[xenou]

I am trying to run the SQL statement from Excel VBA but there seems to be a syntax error

Why do you think there is a syntax error? Is there an error message? Which line are you getting the error on?

 

[mahmed1]

Hi

its showing an error on the part i have named the count column ""TOTAL INCOMING"" and when trying to concatenate the date and time variables to the to_date functions for >= the StartDate and StartTime and <= The EndDate and EndTime

 

[xenou]

1) Does your sql query work directly in oracle?
2) You also haven't said why you think its an error - are you getting an error message from the Oracle server saying the syntax is wrong? What is the error message?

 

[Norie]

You are missing line continuation characters and quotes in the code for the SQL statement.

Try this.

strSQL = " SELECT TS_MOVEMENT," & _
             " NM_DEPARTMENT," & _
             " NM_ORGANISATION," & _
             " TP_ACTIVITY," & _
             " DS_ACT_TYPE," & _
             " ACTION," & _
             " TEAM_FROM," & _
             " HH_NHH," & _
             " COUNT(DS_ACT_TYPE) as 'TOTAL INCOMING'" & _
             " FROM BTAYLO2.BI_INCOMING_AQS_DETAIL" & _
             " WHERE TS_MOVEMENT>= To_Date('18/10/2016 00:00:00','DD/MM/YYYY HH24:MI:SS')" & _
             " AND TS_MOVEMENT<= To_Date('18/10/2016 23:59:59','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"

 

[mahmed1]

Thank you

ill give that a go - how can i encorporate the Date and Time variables for the to_date function?