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')
Comments
Post a Comment