How can i get only time from date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How can i get only time from date

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-02 : 07:14:38

SELECT CONVERT(VARCHAR(25), GETDATE(), 108)

I am getting output as (04:28:56 ). But i need time with AM or PM like
04:28:56 PM

how to write query for this?

G. Satish

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 07:36:33

SOURCE: http://www.fmsinc.com/free/NewTips/SQL/AM_PM_time_format_in_SQL.asp
SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5)
+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

No, you're never too old to Yak'n'Roll if you're too young to die.

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-02 : 07:44:03

how can i give this as default value for binding for particualr column in a table.

quote:
Originally posted by webfred

SOURCE: http://www.fmsinc.com/free/NewTips/SQL/AM_PM_time_format_in_SQL.asp
SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5)
+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

No, you're never too old to Yak'n'Roll if you're too young to die.

G. Satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 08:31:06

[code]SELECT STUFF(REPLACE(RIGHT(CONVERT(CHAR(26), GETDATE(), 9), 14), ' ', '0'), 9, 4, ' ')[/code]

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-02 : 08:35:03

Hi see this link it will be helpful for u

http://www.sql-server-helper.com/tips/date-formats.aspx

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-02 : 08:39:10

i want to set the default value for datetime column in the format like 04:28:56 PM. How can i set this?

quote:
Originally posted by Peso

SELECT STUFF(REPLACE(RIGHT(CONVERT(CHAR(26), GETDATE(), 9), 14), ' ', '0'), 9, 4, ' ')

E 12°55'05.63"
N 56°04'39.26"

G. Satish

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-02 : 08:41:31

see this link

http://dev-for-fun.blogspot.com/2007/08/add-default-value-to-existing-column.html

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 08:55:57

quote:
Originally posted by satish.gorijala

how can i give this as default value for binding for particualr column in a table.

G. Satish

What is the data type of your column ? varchar() or datetime ?

If it is datetime, why do you want the time as "04:28:56 PM" ?
If it is varchar , why are you storing time in a string ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-02 : 09:04:27

Store it as datetime and let the front end application do formation

Madhivanan

Failing to plan is Planning to fail

vision99
Starting Member

14 Posts

Posted - 2009-06-02 : 10:12:42

try this query.....

SELECT
CASE WHEN datepart(hh,GETDATE()) < 12 THEN CONVERT(VARCHAR,datepart(hh,getdate())) + ':' + CONVERT(VARCHAR,datepart(mi,getdate())) + 'AM'
WHEN datepart(hh,GETDATE()) = 12 THEN CONVERT(VARCHAR,datepart(hh,getdate())) + ':' + CONVERT(VARCHAR,datepart(mi,getdate())) + 'PM'
ELSE CONVERT(VARCHAR,datepart(hh,getdate()) - 12) + ':' + CONVERT(VARCHAR,datepart(mi,getdate())) + 'PM'
END

-Vision

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources