CODEWITHSUNDEEP
sql-servert-sql
Arianule
Arianule

Reputation: 9063

Update date to a specific format tsql error

I need to add a current date to and existing data table and this date needs to be in a specified format 'DDMMYYYY'. The column in the table is of the datatype 'date'.

My approach is as follow to which I get the error

Conversion failed when converting date and/or time from character string.

Code

DECLARE @DateNow VARCHAR(10)

SET @DateNow = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '')

UPDATE UserTable
SET DateCreated =  CONVERT(date, @DateNow, 104)
WHERE EmployeeNumber = 'emp00001'

I am a bit rusty in SQL so sorry if this approach seems silly

Regards

Upvotes: 1

Views: 96

Answers (1)

marc_s
marc_s

Reputation: 755157

Why are you converting a DATETIME returned by GETDATE() into a VARCHAR(10) first, and then back into a DATE?? This seems utterly point- and needless....

Try this instead:

DECLARE @DateNow DATE

SET @DateNow = CAST(GETDATE() AS DATE)

UPDATE dbo.UserTable
SET DateCreated = @DateNow
WHERE EmployeeNumber = 'emp00001'

and SQL Server is usually nice enough to not even require any of this converting at all - this will work just fine, too:

UPDATE dbo.UserTable
SET DateCreated = GETDATE()   -- or preferably: SYSDATETIME()
WHERE EmployeeNumber = 'emp00001'

The GETDATE() output (or preferably: SYSDATETIME() as of SQL Server 2008 or newer) will be adapted to match the datatype of your DateCreated column (as long as it's something date-related, of course)

Update: if you need to convert the DATE to a DDMMYYYY format for 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 and newer, you can use the FORMAT function:

SELECT FORMAT(CreatedDate, 'yyyyMMdd')

Upvotes: 2

Related Questions