how to convert nvarchar to time ,not datetime ?

Question

DECLARE @DateNow smalldatetime
SET @DateNow='12:30'
select @DateNow
-------------------------------------OR--------------------------------------
select CAST( '12:30' as datetime )

---

Result: 1900-01-01 12:30:00.000 (i don't want this)

But i need this result in time format not string not datetime?

Result: 12:30 (i want this)

Answer 1

You can use the CONVERT function.

By the way, there's no "TIME" datatype in SQL Server. So, the converted result will always be a STRING.

EDIT: There's no "TIME" datatype in SQL Server versions < SQL Server 2008.

Answer 2

There's a TIME type in SQL Server 2008, but in previous versions, you can represent it as a varchar for display.

This is how you can retrieve the time portion of a DATETIME field in the format you want.

DECLARE @DateNow smalldatetime
SET @DateNow = GETDATE() -- 2009-05-08 12:58:02.680

SELECT CONVERT(CHAR(5), @DateNow, 8) 
-- this returns the time portion as 12:58

Answer 3

If you need to compare, add and/or subtract time (only) values, think about the possibility to use a INT to store the "TIME" value and then to CONVERT it to CHAR when displaying the result.

Answer 4

Like José said, you can use CONVERT to display a datetime as date. MSDN has a list of all possible formats. For example format 8 is hh:mi:ss:

 select convert(varchar(32),getdate(),8)
 12:51:21

Now, you can cut the seconds off by specifying less characters:

 select convert(varchar(5),getdate(),8)
 12:51

Another often used format is 121, yyyy-mm-dd hh:mi:ss.mmm(24h):

 select convert(varchar(32),getdate(),121)
 2009-05-08 12:51:21.987

Where you can pick the time part like:

 select substring(convert(varchar(32),getdate(),121),12,5)
 12:51

Or to combine the string trickeries:

 select right(convert(varchar(16),getdate(),121),5)
 12:51

Right? Right!