sql server - Update date to a specific format tsql error -

i need add current date , existing data table , date needs in specified format 'ddmmyyyy'. column in table of datatype 'date'.

my approach follow error

conversion failed when converting date and/or time character string.

code

declare @datenow varchar(10)  set @datenow = replace(convert(varchar(10), getdate(), 103), '/', '')  update usertable set datecreated =  convert(date, @datenow, 104) employeenumber = 'emp00001' 

i bit rusty in sql sorry if approach seems silly

regards

why converting datetime returned getdate() varchar(10) first, , date?? seems utterly point- , needless....

try instead:

declare @datenow date  set @datenow = cast(getdate() date)  update dbo.usertable set datecreated = @datenow employeenumber = 'emp00001' 

and sql server nice enough not require of converting @ - work fine, too:

update dbo.usertable set datecreated = getdate()   -- or preferably: sysdatetime() employeenumber = 'emp00001' 

the getdate() output (or preferably: sysdatetime() of sql server 2008 or newer) adapted match datatype of datecreated column (as long it's date-related, of course)

update: if need convert date ddmmyyyy format output, i'd use this:

select       replace(convert(varchar(20), createddate, 104), '.', '') 

which converts today's date into

27112014 

or if you're using sql server 2012 , newer, can use format function:

select format(createddate, 'yyyymmdd')