2

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 !

Improve this question
4
  • 4
    time has a scale. Try time(0). On a different note, why are you storing dates as a varchar and not a datetime or datetime2? Also, TRY_PARSE and time were introduced in SQL Server 2012, however, you've tagged SQL Server 2008 and 2005? Commented Nov 11, 2018 at 22:47
  • Before you go any further you should take some time to understand data types. Also it's important to understand that this kind of formatting is usually best achieved in the 'presentation layer'. If this is for a report or an application then format it in there, not in the database. Commented Nov 11, 2018 at 22:59
  • 1
    Possible duplicate of SQL Server remove milliseconds from datetime Commented Nov 11, 2018 at 23:43
  • You can cast the column to a time as follows.I am assuming that the column "data" is a date datatype. If yes then, select cast(data as time) from table_1 Commented Nov 12, 2018 at 1:34

4 Answers 4

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
Declare @data varchar(28)='12/23/2016 5:02:15 AM' select Convert(varchar(20),Cast(@data as time),24)
Improve this answer

2 Comments

be sure to add context to your answer, it will help a lot
Also, there is no benefit to converting this to a varchar. Quite the opposite, actually.
0

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.