SQL: All the suggested GetDate () methods are not working

Question

I'm doing an assignment and I was stuck at this place for about couple of days.

Getdate () is not working for this procedure. I used all the suggested methods.

But again I have to use the update statement here. So aren't there any procedure code to make it without using an update statement here?

Update Engineer set Enter_date = getdate() where EmpNo = 'Emp001000'

MySQL or MS SQL Server? Please don't tag products not involved! — jarlh, May 26 '16 at 09:42
@ jarlh - I'm sorry. I thought all comes under the sql database designing. — Ami Kumara, May 26 '16 at 09:46
I cannot see you use "@Temp_Date" anywhere in the SP. Are you trying to return the value "@Temp_Date" and assign it to "@Enter_Date"? If yes then Google for how to return a value from SP — Alex, May 26 '16 at 09:46
I don't think MySQL even has the `GetDate()` function. Date/time is one of the worst areas when it comes to ANSI SQL compliance, many products have their own versions. — jarlh, May 26 '16 at 09:48
@TheGameiswar I tried in that way earlier. But a small red color error underline is showing near the closing bracket of GetDate(). — Ami Kumara, May 26 '16 at 10:08

score 0 · Answer 1 · answered May 26 '16 at 09:50

You don't need @temp_date,change @enter_date to GetDate()

exec sp_Add_New_Engineer @EmpNo='Emp00100', @EngNo= 'E00070' ,
@Eng_Type ='Electrical Engineer', @FName ='Pramila', @LName='Thivagaran', 
@DOB ='1994/04/02', @Eng_Address='53/2, Peradeniya Road, Kandy', @Enter_Date = getdate(), @ProNo = 'P6'

Further I recommend formatting your code ,plus don't prefix your sps with SP_ .Here are some issues you may see when you use SP_

score 0 · Answer 2 · answered May 26 '16 at 09:54

Where did you use @Temp_Date. You declared and assigned a value to it but never used in your remaining code. Please refer this sp below. I used your variable instead of @Enter_Date.

ALTER PROCEDURE sp_Add_New_Engineer (
    @EmpNo VARCHAR(20)
    ,@EngNo VARCHAR(12)
    ,@Eng_Type VARCHAR(50)
    ,@FName VARCHAR(50)
    ,@LName VARCHAR(50)
    ,@DOB VARCHAR(50)
    ,@Eng_Address VARCHAR(100)
    ,@Enter_Date DATETIME
    ,@ProNo VARCHAR(12)
    )
AS
DECLARE @Temp_Date DATETIME

SET @Temp_Date = GETDATE()

BEGIN
    IF (
            (
                SELECT count(*)
                FROM Supervisor
                WHERE EmpNo = @EmpNo
                ) = 0
            )
        AND (
            (
                SELECT count(*)
                FROM Labor
                WHERE EmpNo = @EmpNo
                ) = 0
            )
    BEGIN
        INSERT INTO Engineer (
            EmpNo
            ,EngNo
            ,Eng_Type
            ,FName
            ,LName
            ,DOB
            ,Eng_Address
            ,Enter_date
            ,ProNo
            )
        VALUES (
            @EmpNo
            ,@EngNo
            ,@Eng_Type
            ,@FName
            ,@LName
            ,@DOB
            ,@Eng_Address
            ,@Temp_Date
            ,@ProNo
            )
    END
    ELSE
        PRINT 'Employee Number is already in use'
END

SQL: All the suggested GetDate () methods are not working

2 Answers2