CODEWITHSUNDEEP
sql-serverdatedatetime
christina G
christina G

Reputation: 117

Converting datetime to date and time only

I am concatenating 2 columns in SQL Server, they both have a data type of datetime:

UPDATE dbo.Feban_stg 
SET [Date & Time Posted] = CONCAT(CONVERT(VARCHAR(10), [Date Posted], 110), RIGHT(CONVERT(varchar, [Time Posted], 100), 7))

and so far I always get this error

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

The datatype of date & time posted is datetime as well.

Thanks

Upvotes: 0

Views: 120

Answers (1)

Balde
Balde

Reputation: 590

The problem is that there is not space between the date part and time part. CANCAT just adds strings. Try this:

UPDATE dbo.Feban_stg 
SET [Date & Time Posted] = CONCAT(CONVERT(VARCHAR(10), [Date Posted], 110), ' ', RIGHT(CONVERT(varchar, [Time Posted], 100), 8))

In addition, you need 8 instead 7 in RIGHT function to get the complete hour.

Upvotes: 2

Related Questions