CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2005
Peter Harris
Peter Harris

Reputation: 53

Remove the zeros using SQL Server

Hello I am using SQL Server and I have this column :

data
6/19/2019 3:10:12 PM
12/23/2016 5:02:15 AM

I wanted to extract only the time so I used the following query :

select try_parse([data] as time using 'en-US') from Table_1

But the problem is I get as a result this :

15:10:12.0000000
05:02:15.0000000

All is okay except the 0 I mean I would like to get :

15:10:12
05:02:15

Thank you very much for your help !

Upvotes: 2

Views: 1809

Answers (4)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can extract time using below query

SELECT CONVERT(VARCHAR(8), cast('12/23/2016 5:02:15 AM' as time),108)

The output is- 05:02:15. I can hope this query/answer is self explanatory.

Upvotes: 0

Toga Nama
Toga Nama

Reputation: 3

Try this 

select CONVERT(varchar(8),  data, 108) from Table_1

that only show time (17:00:59) but you can add + between them if you want full format date as dd/MM/yyyy hh:mm:ss

select CONVERT(varchar(10), data, 101) + ' ' + CONVERT(varchar(8), data, 108) from Table_1

They will work if data using DATETIME type

Upvotes: 0

Ali Mirzaie
Ali Mirzaie

Reputation: 99

You can use TIME() with the correct scale to get your desired results.

select Convert(time(0) , Data ) as time from Table_1

time(0) means a scale of 0, which has a precision of 8. This results as HH:MM:SS. Using another scale, like 1, would give you a single digit for the fractional second. You can read up on all of the scales in the MS Docs

Upvotes: 2

Vikram Singh
Vikram Singh

Reputation: 124

Declare @data varchar(28)='12/23/2016 5:02:15 AM'

select Convert(varchar(20),Cast(@data as time),24)

Upvotes: 0

Related Questions