CODEWITHSUNDEEP
sqlsql-server
Root Loop
Root Loop

Reputation: 3162

SQL how to extract date time as value only and combining with other value?

I have 2 columns from a table as below

Time                        ID
2018-06-12 18:24:14:250     116441

How can I extract the time as text value only without any of those - or :? then I want to combine Time and ID as third column Named as UID, add UID to the beginning of the combined value.

Time                        ID         UID
2018-06-12 18:24:14:250     116441     UID-20180612182414250-116441

I tried with CONTAT(Time, ID) AS UID but it returned me value as Jun 12 2018 6:24PM116441, I need last 3 digits of millisecond after second and a - in between.

Upvotes: 1

Views: 103

Answers (5)

Ismail Hawayel
Ismail Hawayel

Reputation: 2453

T-SQL CONVERT function has various flavors to achieve what you looking for along with string concatenation. 

declare @x datetime = GETDATE()
select @x as x, convert(varchar, @x,112) + replace(convert(varchar, @x,114),':','') as y

Upvotes: 1

Rahul Sharma
Rahul Sharma

Reputation: 8311

Try this:

SELECT CONCAT('UID-',REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50),'2018-06-12 18:24:14:250',121),'-',''),':',''), ' ', ''),'-','116441') AS UID

Replace the string(s) with your Time and ID column.

Upvotes: 1

Max
Max

Reputation: 771

Try this

select [Time],
       [ID]
       ('UID-' + FORMAT([Time], 'yyyyMMddHHssmmm') + '-' + ID) as UID
  from YourTable

Upvotes: 1

Thom A
Thom A

Reputation: 96038

It's a "bit" long winded, but I'd probably go with:

SELECT 'UID-' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(23),[time],126),'T',''),':',''),'.',''),'-','') + '-' +CONVERT(varchar(10),ID)
FROM (VALUES(CONVERT(datetime,'2018-06-12T18:24:14.250'),116441))V([time],ID);

If you want to use CONCAT, then it would be:

SELECT CONCAT('UID-',REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(23),[time],126),'T',''),':',''),'.',''),'-',''),'-',ID)
FROM (VALUES(CONVERT(datetime,'2018-06-12T18:24:14.250'),116441))V([time],ID);

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31795

Try using CONCAT() with DATEPART().

Upvotes: 0

Related Questions