Insert into table a from table b HELP 2

[jgurgen]

insert into iProf_Status_History (LoanID, StatusDate, UserName, StatusCode) Values (a,b,c,d)

a,b,c will be coming from a specific table (MortPayoffCalc) where Approved=1

b is a varchar that has datetime vaules. I need to convert them to datetime for the new table.

a : Mortpayoffcalc.LoanID
b : Mortpayoffcalc.ApprovedDate
c : Mortpayoffcalc.ApprovedBy

d : 3

please help writing this script

 

[kaht]

insert into iProf_Status_History (LoanID, StatusDate, UserName, StatusCode) 
select Mortpayoffcalc.LoanID, convert(datetime, Mortpayoffcalc.ApprovedDate), Mortpayoffcalc.ApprovedBy, 3 from Mortpayoffcalc where Approved = 1

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[bborissov]

Did you always have a <b>b</b> field filled?
Did you always have a value in that field that could be converted to DateTime?
If so kaht suggestion should work. If no try (based on kaht example):

insert into iProf_Status_History
       (LoanID, StatusDate, UserName, StatusCode)
select Mortpayoffcalc.LoanID,
       CASE WHEN ISDATE(Mortpayoffcalc.ApprovedDate)
            THEN convert(datetime, Mortpayoffcalc.ApprovedDate)
            ELSE NULL END,
        Mortpayoffcalc.ApprovedBy,
        3
from Mortpayoffcalc where Approved = 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Sorry, you must compare ISDATE() with 1:

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] iProf_Status_History
       (LoanID, StatusDate, UserName, StatusCode)
[COLOR=blue]select[/color] Mortpayoffcalc.LoanID,
       [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]ISDATE[/color](Mortpayoffcalc.ApprovedDate) = 1
            [COLOR=blue]THEN[/color] [COLOR=#FF00FF]convert[/color]([COLOR=#FF00FF]datetime[/color], Mortpayoffcalc.ApprovedDate)
            [COLOR=blue]ELSE[/color] NULL [COLOR=blue]END[/color],
        Mortpayoffcalc.ApprovedBy,
        3
[COLOR=blue]from[/color] Mortpayoffcalc [COLOR=blue]where[/color] Approved = 1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jgurgen]

thank you