Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems with date time formats

Status
Not open for further replies.

brentChch

Technical User
Sep 6, 2009
2
NZ
Hi

I am trying to write a program to get a list of log files and sort by the .DateLastModified then open the most recent and search for text, then the next oldest file, etc.

I thought it would be easiest to insert into a MS SQL table and then query the table and ORDER BY Modified DESC.

I am having trouble with the date and time formats.

I have this SQL command
commandtext = "INSERT INTO tblPrintLogs (Logfile,Modified) VALUES ('" & fileName & "',CONVERT(datetime, '" & file.DateLastModified & "', 103))"

This results in a command
INSERT INTO tblPrintLogs (Logfile,Modified) VALUES ('print.dbg',CONVERT(datetime, '21/08/2009 1:32:49 p.m.', 103))

Which has problems with the p.m.

So
-Is there an easy way to sort files by .DateLastModified so I don't need the SQL table?
-Is there a way to convert the date time easily in VBScript so that it will insert into the DB as datetime? NZ to USA date. AM/PM to 24 hour.
-Is there an easy way to remove the fullstops from the p.m. so that the SQL CONVERT will work? Do I need to remove these from the string, check if AM or PM and append new text back onto the string?
-Are there any other ideas?

Thanks
 
No that there isn't other methods, one way or another. Just to make the fact plain, put this one line (I break it up into two lines to avoid wide-post) in a .vbs file and double click on it.
[tt]
wscript.echo createobject("scripting.filesystemobject") _
.getfile(wscript.scriptfullname).datelastmodified
[/tt]
Does it display p.m. or a.m.? I don't think so, but your setting might. Can you confirm?
 
for sorting you might want to try

Set objArray = CreateObject("System.Collections.ArrayList")

and the .Sort method
 
Hi
Thanks for your replies.

I just put this into the code:

LastModified = file.DateLastModified
If instr(LastModified, "p.m.") <> 0 then
LastModified = left(LastModified, len(LastModified) - 4) & "pm"
elseif instr(LastModified, "a.m.") <> 0 then
LastModified = left(LastModified, len(LastModified) - 4) & "am"
end if
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top