October 14, 2002 at 2:59 pm
I am importing a text file into a temp table every 15 min. and want to compare two fields, PINChangeDate mmddyyyy, and PINChangeTime hhmmssxx, both stored as char(8) and manipulted by ASP files on one end and another program for the text file. I want to compare these val's on the import and update the table in the db if the fields in the temp table are > than those in the base table. What is a good way to accomplish this comparison without running into any unforseen comparison issues. Here is what I have right now. It seems to work. Does anyone see a flaw here? Or is this o.k.?
UPDATE SSnumpin
SET PIN = v.PIN,
FirstTimeFlag = v.FirstTimeFlag,
CallCounter = v.CallCounter,
Lastname = v.LastName,
FirstName_MiddleInitial = v.FirstName_MiddleInitial,
EmpNum = v.EmpNum,
PINChangeFlag = v.PINChangeFlag,
PINChangeDate = v.PINChangeDate,
PINChangeTime = v.PINChangeTime,
PINLength = v.PINLength
from SSnumpinVRU v JOIN SSnumpin s ON (v.SSNum = s.SSNum)
WHERE v.PINChangeDate > s.PINChangeDate
AND v.PINChangeTime > s.PINChangeTime
October 15, 2002 at 8:30 am
I think this will work fine as long as you always have 8 digits. Such that the date 1/1/2002 shows as 01012002 and not 112002 and the time for 3:40 AM shows as 03400000 and not 3400000.
Do you have a way to make sure all 8 digits are always filled and that they are numbers? If not you can use a check constraint.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 15, 2002 at 8:51 am
Thanks Robert. Yes, there's validation on both sides that manipulate the text file and the SQL table to always produce these char(8)length fields. I had thought this would be fine and appears to be so in testing. Just wanted to run it by the SQL crowd before putting into production to be sure since there are bucks invloved. Thanks for the response. I appreciate it.
-Pat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply