rrhandle8
asked on
Convert DateTime format in SQL query
A existing SQL statement that converts DateTime to a different format has been working for a long time, but is now give me a "Query timeout expired" error only seconds after the query is executed.
Desired formate is: 03/21/2017 18:53:00
Is there another/better way to achieve the desired outcome?
SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends order by LogicName"
Original DateTime is like this: 3/21/2017 6:53:00 PMDesired formate is: 03/21/2017 18:53:00
Is there another/better way to achieve the desired outcome?
what is your TimeStamp datatype?
ASKER
Sorry, I do not know how to answer that. I assume it is DateTime in the database, and I need it as text.
what about this
SELECT convert(varchar, getdate(), 103) + right(convert(varchar, getdate(), 120),9)
26/05/2017 23:51:43
ASKER
Almost worked.
26/05/2017 23:51:43 should be 05/26/2017 23:51:43
26/05/2017 23:51:43 should be 05/26/2017 23:51:43
ASKER
103 should be 101. OK that works. Let's see if it solves the problem I am having.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That seems to be working. Do you see anything wrong with the original code I was using?
you have
2 + and 4 convert operation, whereas my suggestion has
1 + and 2 convert
2 + and 4 convert operation, whereas my suggestion has
1 + and 2 convert
ASKER
I made the change, but still having the same problem. Waiting to hear back from the client to see if the LogicName field is indexed.
dont you have any index on this table?
what is PK? is it identity?
what is PK? is it identity?
ASKER
Primary Key is ID and it is indexed.
you need ındex on LogicName, not ID
or PK on ID
IX on LogicName, 2 index needed...
or PK on ID
IX on LogicName, 2 index needed...
ASKER
The PK has always been there. I added the Index on the LogicName field today. There was no difference. The query runs if there is no "order by" clause. With the "Order By" clause, it times out in 30 seconds.
ASKER
function strFileName()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Special addition to filename
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
yyyy = Year(Now)
mm = Month(Now): If Len(mm) = 1 Then mm = "0" & mm
dd = Day(Now): If Len(dd) = 1 Then dd = "0" & dd
hh = Hour(Now): If Len(hh) = 1 Then hh = "0" & hh
mmm = Minute(Now): If Len(mmm) = 1 Then mmm = "0" & mmm
ss = Second(Now): If Len(ss) = 1 Then ss = "0" & ss
strFileName = yyyy & mm & dd & hh & mmm & ss & ".csv"
End function
function strFTPName()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Special addition to individual filenames that get FTPed
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
yyyy = Year(Now)
mm = Month(Now): If Len(mm) = 1 Then mm = "0" & mm
dd = Day(Now): If Len(dd) = 1 Then dd = "0" & dd
hh = Hour(Now): If Len(hh) = 1 Then hh = "0" & hh
mmm = Minute(Now): If Len(mmm) = 1 Then mmm = "0" & mmm
ss = Second(Now): If Len(ss) = 1 Then ss = "0" & ss
strFTPName = yyyy & mm & dd
End function
'Sub SyncShell(ByVal Cmd As String)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' We are now creating separate files for each LogicName,
' so each file must be FTPed individually while the script
' is running.
' SyncShell is used to force the script to wait for the
' FTP to finish
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 'VBA.CreateObject("WScript.Shell").Run Cmd, 1, True
'End Sub
Sub deletefiles()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' The last thing to do is delete the files.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim fs, fo, x
Set fs = CreateObject("Scripting.FileSystemObject")
Set fo = fs.GetFolder("D:\dbcsv\")
target = strFPTName & "__"
For Each x In fo.Files
Debug.Print (x.Name)
'Print the name of all files in the test folder
If InStr(x.Name, target) > 0 Then
Debug.Print (x.Name)
fs.DeleteFile (x.Name)
End If
Next
Set fo = Nothing
Set fs = Nothing
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Settings
'
' All you have to do is get these setting right.
'''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DataSource
Dim DatabaseName
Dim DBUser
Dim DBPassword
Dim ftpSite
Dim ftpUser
Dim ftpPassword
Dim ftpExePath
Dim SQL
Dim FileName
DataSource = "127.0.0.1"
DatabaseName = "InetTrends0"
DBUser = "xxxxxx"
DBPassword = "xxxxxxxxx"
''' >>> SQL = "SELECT top 10000000 * FROM Trends"
'SQL = "SELECT top 10000000 TimeStamp, PointID, PointValue FROM Trends"
'SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends"
SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends order by LogicName"
''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim objConnection,objRecordset,strSearchCriteria
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=sqloledb;Data Source=" & Datasource & ";Initial Catalog=" & DatabaseName & ";User Id=" & DBUser & ";Password=" & DBPassword
objRecordset.Open SQL, objConnection, adOpenStatic, adLockOptimistic
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add for writing to text file
''''''''''''''''''''''''''''''''''''''''''''''''''''''
dim fso
Dim tst
Dim i
Dim fld
i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
''''''''''''''''''''''''''''''''''''''''''''''''''''''
FileName = strFileName()
Set tst = fso.OpenTextFile(FileName, 2, True)
while not objRecordset.eof
Field_Cnt = 0
For Each fld in objRecordset.Fields
Field_Cnt = Field_Cnt + 1
if Field_Cnt = 3 then 'You have 4 fields? Or was it 3? Adjust the field_Cnt accordingly
tst.Write objRecordset(fld.Name)
else
tst.Write objRecordset(fld.Name) & chr(44)
End If
Next
tst.Write vbcrlf
i = i + 1
objRecordset.MoveNext
Wend
tst.close
Set tst = Nothing
objRecordset.Close
objConnection.Close
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Now create separate files for each unique LogicName,
' then ftp the file.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''fileName = D:\dbcsv\filename" & "20170407145539.csv" ' <-- Testing only
Set fso = CreateObject("Scripting.FileSystemObject")
Set tst = fso.OpenTextFile(fileName, 1)
Set f = fso.CreateTextFile("D:\dbcsv\" & "NoNameFile.csv", True)
f.WriteLine ("NoNameFile")
Do While tst.AtEndOfStream = False
TextLine = (tst.ReadLine)
temp = Split(TextLine, ",")
''''''''''''''''''''''''''''''''''''''''''''''''''
' Filter out forbidden characters for folder names
''''''''''''''''''''''''''''''''''''''''''''''''''
LogicName = temp(1)
LogicName = Replace(LogicName, ":", "_")
LogicName = Replace(LogicName, "/", "_")
LogicName = Replace(LogicName, "%", "_")
LogicName = Replace(LogicName, "&", "_")
LogicName = Replace(LogicName, "*", "_")
LogicName = Replace(LogicName, "\", "_")
LogicName = Replace(LogicName, "#", "_")
LogicName = Replace(LogicName, "{", "_")
LogicName = Replace(LogicName, "}", "_")
LogicName = Replace(LogicName, "+", "_")
LogicName = Replace(LogicName, "|", "_")
If LastLogicName <> LogicName Or LastLogicName = "" Then
' A new file needs to be opened.
' Close current file.
f.Close
Set f = Nothing
' FTP the current file if LastLogicName <> ""
if LastLogicName <> "" then
end if
' Create the new file
Prefix = strFPTName
Set f = fso.CreateTextFile("D:\dbcsv\" & Prefix & "__" & LogicName & ".csv", True)
LastLogicName = LogicName
Else
f.WriteLine (TextLine)
End If
Loop
f.Close
Set f = Nothing
tst.Close
Set tst = Nothing
Set fso = Nothing
deletefiles