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]

Do you mean replace '18/10/2016 00:00:00','DD/MM/YYYY HH24:MI:SS' and '18/10/2016 23:59:59','DD/MM/YYYY HH24:MI:SS' here

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

with the values in StartDate, EndDate, StartTime, and EndTime?

If so how are those values formatted?

 

[mahmed1]

Thats right buddy

well the times are stored in a range as hh:mm:ss and then passed to 2 comboboxes
the start time value selected from
the combo box is passed to starttime variable and the same is done for the endtime where the value is passed to the end time

the dates are picked from 2 datepickers so not sure exactly how or what they are stored under and the value selected of both is passed to the startdate and enddate variable

1 thing i have done is that i wrapped the format around the dates picked using dd/mm/yyyy format and the same for the times formatted as hh:mm:ss

because i wernt sure about the data types i left the 4 variables startdate, enddate, starttime and endtime blank

hopefully you can advise better to which 1 i should select and should be

 

[Mick Peters]

Hi Lesceline,

Could this syntax work for you ?select
start_tran_date,trunc(start_tran_date) as My_Date, To_char(start_tran_date,'HH24:MI:SS') as "MyTime" from table 1;

I hope this helps ,
Mick.

 

[Mick Peters]

Hi Lesceline
Or you could try putting it all in a single statement and enclose that in your SQL, Try this an see if it works,


SELECT
* FROM Table1 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'06:00:00';

I hope this helps ,
Mick.

 

[mahmed1]

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"

Hi Norie,

I tried this but its still showing error on this part
" 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') & _

 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('" & 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"

 

[Norie]

You are missing " again, specifically after the ) in these 2 lines.

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

 

[mahmed1]

[QUOTE=Norie;4662506]You are missing " again, specifically after the ) in these 2 lines.

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

[/QUOTE]

Doh you are right - thank you


This is how im stroring the dates and times
Me.DTPickerStart.Value = Format(Date, "DD/MM/YYYY")
Me.DTPickerEnd.Value = Format(Date, "DD/MM/YYYY")
Me.CboTimeStart.Value = Format(Idx.Range("A3").Value, "HH:MM:SS")
Me.CboTimeEnd.Value = Format(Idx.Range("B3").Value, "HH:MM:SS")

Based on that - what data types should the following variables be and why

StartDate = IncomingFrm.DTPickerStart.Value
EndDate = IncomingFrm.DTPickerEnd.Value
StartTime = IncomingFrm.CboTimeStart.Value
EndTime = IncomingFrm.CboTimeEnd.Value

Thank YOu

 

[mahmed1]

You are missing " again, specifically after the ) in these 2 lines.

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

Doh you are right - thank you


This is how im stroring the dates and times
Me.DTPickerStart.Value = Format(Date, "DD/MM/YYYY")
Me.DTPickerEnd.Value = Format(Date, "DD/MM/YYYY")
Me.CboTimeStart.Value = Format(Idx.Range("A3").Value, "HH:MM:SS")
Me.CboTimeEnd.Value = Format(Idx.Range("B3").Value, "HH:MM:SS")

Based on that - what data types should the following variables be and why

StartDate = IncomingFrm.DTPickerStart.Value
EndDate = IncomingFrm.DTPickerEnd.Value
StartTime = IncomingFrm.CboTimeStart.Value
EndTime = IncomingFrm.CboTimeEnd.Value

Thank YOu

Hi Buddy - I am also getting an error on this line of code now saying FROM keyword not found where specified - Im not sure its because the time is probably being passed as string not sure though as the SQL string after debugging seems fine

rs.Open strSQL, cn, adOpenStatic, adLockReadOnly

 

[mahmed1]

My Full code: Getting an error when opening the RS - this error (RUN TIME ERROR 2147467259) - ORA EXPRESSION MISSING

Not sure what data types the 4 variables should be

StartDate = IncomingFrm.DTPickerStart.Value
EndDate = IncomingFrm.DTPickerEnd.Value
StartTime = IncomingFrm.CboTimeStart.Value
EndTime = IncomingFrm.CboTimeEnd.Value

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
    'Database = "???"     '(Schema Details)
    'UserId = "????" '(MyLogin UserId)
    'Password = "?????" '(MyPassword)
    
      
    '//Connection string  'Provider
    connection_string = "Provider=OraOLEDB.Oracle.1;Password=" & Password & ";Persist Security Info=False;User ID=" & UserId & ";Data Source=" & Database & ";Extended Properties=PLSQLRSet=1;DistribTX=0;OLE DB Services = -1"
                        
'    '//USE SQL Command to Update, Insert, Create (Paramaters, Append to parameters), Run Stored Procedures etc...)
'    sSQL = "UPDATE Table1 SET ProdDesc = 'Chocolate Frogs' WHERE ProdID = 'CF001';"
'
'    '//Open a connection with the connection string
    
    'On Error GoTo ConnectionError:
    
    With cn
        .ConnectionString = connection_string
        .Open
    End With
    
         
    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) " & _
                    " 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"
 
Debug.Print strSQL
'    With cmd
'        .ActiveConnection = cn ' Open Connection using ADODB Connection
'        .CommandType = adCmdText
'        .CommandText = sSQL 'Use this Procedure (Could be SQL script, Stored Procedures) etc
'        .Execute ' Run the Command Text
'    End With
    '//SQL Command to input data from recordset
             
    'Runs SQL statement and stores in memory
    'On Error GoTo ConnectionError:
    
    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
ConnectionError:
     
     
On Error Resume Next
     
     cn.Close
     rs.Close
     
     '//Cleanup (optional)
     Set rs = Nothing
     Set cn = Nothing
     Set cmd = Nothing
     strSQL = ""
End Sub

 

[xenou]

Debug.Print strSQL

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 (?))