Updating timestamp with custom format (dd/MM/yyyy HH:MM:ss.FFFFFFF)

Question

How to write a statement that will update the date field in database with dd/MM/yyyy HH:MM:ss.FFFFFFF format.

I have select query which return the require string

SELECT FORMAT(CURRENT_TIMESTAMP, 'dd/MM/yyyy HH:MM:ss.FFFFFFF')

I tried with

update ORDER 
set timedate=FORMAT(CURRENT_TIMESTAMP, 'dd/MM/yyyy HH:MM:ss.FFFFFFF') 
WHERE ID='288'

But returning error :

SQL Error [8152] [22001]: String or binary data would be truncated. com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.

My field datatype is varchar 27

CREATE TABLE AYAM.dbo.ORDER (
ID int NOT NULL IDENTITY(1,1),
TIMEDATE varchar(27) NOT NULL,
CONSTRAINT PK_ORDER_DATA PRIMARY KEY (ID,TIMEDATE)
) GO;

I am using MSSQL 2016

Answer 1

Why do you ask for FFFFFFF when CURRENT_TIMESTAMP function returns only 3 decimal numbers? Use SYSDATETIME() function to get better precision.

I was unable to re-produce the error. What SQL Server version do you use?

create table #test (timedate varchar(27))
insert into #test VALUES ('test');
update #test 
set timedate=FORMAT(SYSDATETIME(), 'dd/MM/yyyy HH:MM:ss.FFFFFFF') 
select * from #test
drop table #test

The output is: 18/09/2017 12:09:44.6914345

UPDATED: the same test was done using your table structure. No errors ...

INSERT INTO dbo.[ORDER] (TIMEDATE) VALUES ('test')
GO 300


update [ORDER] 
set timedate=FORMAT(CURRENT_TIMESTAMP, 'dd/MM/yyyy HH:MM:ss.FFFFFFF') 
WHERE ID='288'