user2992655 user2992655 - 4 months ago 13
SQL Question

can datetime type of sql server hold time only?

I'm trying to store time in format


05:00 PM


in database. but when i insert the data it automatically stores date as well like


2016-07-20 17:00:00.000


All i want only


17:00


in database

Answer

You cant store Time only in a datetime field it stores default date,Use TIME data type if you are on 2008..

declare @t datetime
select @t=cast(getdate() as time)
print @t
---Jan  1 1900  9:17AM

declare @t time
select @t=cast(getdate() as time)
print @t
---09:17:48.3330000

for sql 2008

select cast(getdate() as time)
--09:17:25.4400000

for 2005

select convert(varchar(10),getdate(),108)
---09:17:33

your exact format

select cast(datepart(hour,getdate()) as varchar(10))+':'+ cast(datepart(minute,getdate()) as varchar(10))
---9:17

You also can use FORMAT to store only time (From SQl 2012)

select FORMAT(GETDATE(),'HH:MM')
--09:07
Comments