CODEWITHSUNDEEP
sqlasp.netsql-serverdatabase
Farhan ALi
Farhan ALi

Reputation: 63

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

Upvotes: 0

Views: 485

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

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

Upvotes: 1

blackbishop
blackbishop

Reputation: 32640

First, as suggested in the comments, you should use time type instead of datetime.

The format of time in SQL Server is : hh:mm:ss or hh:mm:ss.nnnnnnn. If you want time to be in AM PM format just use this :

CONVERT(varchar(15),CAST('17:00:00.000' AS TIME),100)

Upvotes: 1

Related Questions