Link to home
Start Free TrialLog in
Avatar of rrhandle8
rrhandle8Flag for United States of America

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.

SQL = "SELECT top 1000000 CONVERT(VARCHAR(10),TimeStamp,101) + ' ' + CONVERT(CHAR(8),(CONVERT(DATETIME,TimeStamp,113)),114), LogicName, PointValue FROM Trends order by LogicName"

Open in new window

Original DateTime is like this: 3/21/2017 6:53:00 PM
Desired formate is: 03/21/2017 18:53:00
Is there another/better way to achieve the desired outcome?
Avatar of HainKurt
HainKurt
Flag of Canada image

what is your TimeStamp datatype?
Avatar of rrhandle8

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

Open in new window

Almost worked.
26/05/2017 23:51:43 should be 05/26/2017 23:51:43
103 should be 101.  OK that works.  Let's see if it solves the problem I am having.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
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...
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.
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

Open in new window