Technical Forum

SIMATIC WinCC

sql date time comparing in wincc vbscript

Thread Starter: Yalcin   Started: 3/22/2013 8:04 AM   Replies: 2

« Previous thread Next thread »
Page 1 of 1 (3 items)
  3/22/2013, 8:04 AM
Joined 12/25/2006
Last visit: 6/2/2014
Posts: 40
Rating:
Rated: Very Good [2 out of 5 / rated 1 time(s)]. (1) 
Rated: no ratings [0 out of 5 / rated 0 time(s)]. (0) 
Hello 

I have an script reading value from sql but I cant compare datetime value between sql and wincc 

the script is 

Option Explicit
Function action
Dim excelobject, conn, rst, i, ilktarih, sontarih, YR, MNTH, DY, HR,MIN,SEC,YR_1, MNTH_1, DY_1, HR_1, MIN_1, SEC_1, Sql_Table
On Error Resume Next

Dim BULK_COPY
Set BULK_COPY = HMIRuntime.Tags("BULK_COPY")
BULK_COPY.Read

If BULK_COPY.Value = 1 Then
Dim gun_ilk
Set gun_ilk = HMIRuntime.Tags("gun_ilk")
Dim gun_son
Set gun_son = HMIRuntime.Tags("gun_son")
Dim ay_ilk
Set ay_ilk = HMIRuntime.Tags("ay_ilk")
Dim ay_son
Set ay_son = HMIRuntime.Tags("ay_son")
Dim yil_ilk
Set yil_ilk = HMIRuntime.Tags("yil_ilk")
Dim yil_son
Set yil_son = HMIRuntime.Tags("yil_son")
Dim saat_ilk
Set saat_ilk = HMIRuntime.Tags("saat_ilk")
Dim saat_son
Set saat_son = HMIRuntime.Tags("saat_son")
Dim dak_ilk
Set dak_ilk = HMIRuntime.Tags("dak_ilk")
Dim dak_son
Set dak_son = HMIRuntime.Tags("dak_son")
Dim saniye_ilk
Set saniye_ilk = HMIRuntime.Tags("saniye_ilk")
Dim saniye_son
Set saniye_son = HMIRuntime.Tags("saniye_son")
YR=Year(yil_ilk.read)
MNTH=Month(ay_ilk.read)
DY=Day(gun_ilk.read)
HR=Hour(saat_ilk.read)
MIN=Minute(dak_ilk.read)
SEC=Second(saniye_ilk.read)
YR_1=Year(yil_son.read)
MNTH_1=Month(ay_son.read)
DY_1=Day(gun_son.read)
HR_1=Hour(saat_son.read)
MIN_1=Minute(dak_son.read)
SEC_1=Second(saniye_son.read)
 
  ilktarih = DY & "." & MNHT & "." & YR & " " & HR & ":" & MIN & ":" & SEc
  sontarih = DY_1 & "." & MNHT_1 & "." & YR_1 & " " & HR_1 & ":" & MIN_1 & ":" & SEC_1

Set excelobject = CreateObject("excel.application")
excelobject.Workbooks.Add()
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
On Error Resume Next
conn.Open "Provider=MSDASQL;Initial Catalog=kalinlik;DSN=KALINLIK"
Sql_Table= "SELECT * FROM cikis " 
Set rst = conn.Execute(Sql_Table)
'excelobject.Visible = False
'excelobject.Workbooks.open("C:\Rapor.xlsx")
'excelobject.Activesheet.Range("A2:65536").select
'excelobject.Activesheet.selection.clearcontents
excelobject.ActiveWorkbook.saveas("C:\Rapor.xls")
excelobject.Activesheet.Range("A1").value = rst.Fields(0).name
excelobject.Activesheet.Range("B1").value = rst.Fields(1).name
excelobject.Activesheet.Range("C1").value = rst.Fields(2).name
excelobject.Activesheet.Range("D1").value = rst.Fields(3).name
excelobject.Activesheet.Range("A1").columns.autofit
excelobject.Activesheet.Range("B1").columns.autofit
excelobject.Activesheet.Range("C1").columns.autofit
excelobject.Activesheet.Range("D1").columns.autofit
excelobject.Activeworkbook.Save()
excelobject.Quit    
    conn.close
    
    conn.Open "Provider=MSDASQL;Initial Catalog=kalinlik;DSN=KALINLIK"

Sql_Table = " insert into OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Rapor.xls;HDR=yes', 'Select CIKIS_KAFA_1, CIKIS_KAFA_2, CIKIS_KAFA_3, CIKIS_TARIH from [Sayfa1$]' ) select ROUND(CIKIS_KAFA_1, 2), ROUND(CIKIS_KAFA_2, 2), ROUND(CIKIS_KAFA_3, 2), CIKIS_TARIH from cikis where CIKIS_TARIH >=  '" & ilktarih & "' And CIKIS_TARIH <= '" & sontarih & "' "'ORDER By CIKIS_TARIH DESC " 
Set rst = conn.Execute(Sql_Table)
If Err.Number <> 0 Then
MsgBox "copy #" & Err.Number & " " & Err.Description
Err.Clear
'Exit Sub
End If
conn.close
 
Set rst = Nothing
Set conn = Nothing
excelobject.Visible = True
excelobject.Workbooks.open("C:\Rapor.xls")
Dim lutfen
Set lutfen = HMIRuntime.Tags("lutfen")
lutfen.Read
lutfen.value=0
lutfen.write

BULK_COPY.Value=0
BULK_COPY.Write


End If



BULK_COPY.Value=0
BULK_COPY.Write

End Function

when I seperate the where function it works but with where function it can't work 

What can I do



=== Edited by Yalcin @ 3/22/2013 1:00 PM [GMT ] ===



automation and machine desing, project manager, drawing,programing, and automation responsable
Top
  3/22/2013, 12:02 PM
Joined 10/5/2010
Last visit: 9/18/2014
Posts: 1526
Rating:
Rated: Outstanding [3.96 out of 5 / rated 48 time(s)]. (48) 
Users with special expertise supervising conferences
Rated: no ratings [0 out of 5 / rated 0 time(s)]. (0) 
Hello Yalcin,

I'm sorry, but I can't see any Where Clause in your programm.
Is there some kind of missunderstanding?

Best regards

Bizur
Top
  3/22/2013, 1:00 PM
Joined 12/25/2006
Last visit: 6/2/2014
Posts: 40
Rating:
Rated: Very Good [2 out of 5 / rated 1 time(s)]. (1) 
Rated: no ratings [0 out of 5 / rated 0 time(s)]. (0) 
YOU CAN SEE THE BOLD FONTS

automation and machine desing, project manager, drawing,programing, and automation responsable
Top
Page 1 of 1 (3 items)
Actions