1

I have a column with HHMMSS in a table.

Similar to this out put: 

SELECT convert(varchar, getdate(), 108)

I need to be able to make a view in MSSMS with only the HH from time and convert it as an int.

Example Time 

11:08:11, 
12:08:12
Int
11
,12

I have tried several tricks from this page but could not find any work around https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Max
  • 67
  • 2
  • 5
  • Please have a look into the thread https://stackoverflow.com/questions/1114307/extracting-hours-from-a-datetime-sql-server-2005 – Suraj Kumar Jan 24 '19 at 10:20
  • I am confused, what is "Example Time" here, and how the accepted answer handling the sample example time? – PSK Jan 24 '19 at 10:49

3 Answers3

4

Why not just use DATEPART? There's no need to convert the value from a datetime to a varchar and then to an int.

SELECT DATEPART(HOUR, GETDATE());

Edit: on a different note, I strongly suggest you always ensure you declare your length, precision and scale when using a datatype. SELECT CONVERT(varchar, GETDATE(), 108) will return the whole value here, as (in this case) this it converts the value to a varchar(25), however, not declaring these parameters can/does leads to unexpected behaviour.

I've seen many questions where people have asked "Why isn't this working?" because their SP is declared as CREATE PROC MyProc @String varchar AS ..., or they have a variable declaration of DECLARE @MyVar nvarchar;, which in both cases means the length has a value of 1; and thus their values are truncated.

Edit for Irony: And no less than an hour later... Substring function not comparing the output; looks like I need to order a new crystal ball. Exactly why not declaring your Length, Precision or Scale is a bad idea.

Thom A
  • 88,727
  • 11
  • 45
  • 75
1

One way could be this:

SELECT   LEFT(convert(varchar, getdate(), 108),2)
apomene
  • 14,282
  • 9
  • 46
  • 72
0

You can use stuff() & do conversations :

select col, datepart(hh, cast(stuff(stuff(col, 3, 0, ':'), 6, 0, ':') as time(0)))
from table t;

This assumes col has varchar type time.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52