Error in Updating a table using Stored procedure

In this stored procedure there is an error occured. That is 'format' is not a recognized built in function name

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[attendance_updatebyemployee_id]
@Employee_id int,
@AtDate datetime,
@FNLogged bit,
@ANLogged bit,
@LogTime varchar(10),
@LogOuttime varchar(10) 
AS
BEGIN   

select @AtDate 



SET NOCOUNT ON;    
    update Mst_Attendance set FNLogged=@FNLogged,
   ANLogged=@ANLogged,LogTime=@LogTime,LogOuttime=@LogOuttime 
   开发者_C百科where EmployeeId=@Employee_id and Atdate = FORMAT(@AtDate,'MM/DD/YYYY')

select * from Mst_Attendance where Atdate=@AtDate and EmployeeId=@Employee_id


END

What database and version are you using? By the syntax, I assume it is MS SQL Server, but what version? 2000, 2008?

In MS SQL Server, there is no FORMAT function. But you can use CONVERT.

CONVERT(VARCHAR(10), @AtDate ,101)

101 here means 'MM/DD/YYYY'. For other formats, check the Official SQL Documentation

Im assuming you Atdate field in the database is a VARCHAR(10) (or else you wouldnt need any convertion at all). But i strongly advise against that. Whenever possible, use DATETIME for dates, not VARCHAR

Try

 CONVERT(VARCHAR(10), @AtDate, 101) AS [MM/DD/YYYY]

However, surely your AtDate column should be a datetime?

Alternative way to output a DateTime column as a string in MM/DD/YY format:

CONVERT(VARCHAR, @AtDate, 1)

However this is not necessary in your query as the column appears to be a DateTime type already.

More built in date conversion formats:

http://www.technoreader.com/SQL-Server-Date-Time-Format.aspx

FORMAT() is not a SQL Server built-in function.

You can do it like this:

update Mst_Attendance 
set 
    FNLogged=@FNLogged, ANLogged=@ANLogged, 
    LogTime=@LogTime, LogOuttime=@LogOuttime     
where 
    EmployeeId=@Employee_id AND
    Atdate = @AtDate

Please tell me that your Atdate column is a datetime and NOT a varchar()