SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Convert time field to AM/PM
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

snufse
Constraint Violating Yak Guru

USA
469 Posts

Posted - 09/24/2008 :  08:58:29  Show Profile  Reply with Quote
I have a query that selects a "time" field. I need to display time as AM/PM time. Presently time shows as military. Thank you.


myAdsSelectString = "select ticket_number, ticket_date, ticket_time from salestkt where shift_started >= " & "'" & myDateTimeValue & "'" & " order by ticket_id"

sunil
Constraint Violating Yak Guru

India
282 Posts

Posted - 09/24/2008 :  09:10:35  Show Profile  Reply with Quote
This can be done in front end application and is advisable to be done there.
Go to Top of Page

snufse
Constraint Violating Yak Guru

USA
469 Posts

Posted - 09/24/2008 :  09:53:23  Show Profile  Reply with Quote
Since I am using GridView and BoundFileds in my application, I have not had any sucess doing it there.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 09/24/2008 :  10:14:13  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What is the datatype of ticket_time column?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts

Posted - 09/24/2008 :  10:26:24  Show Profile  Visit ayamas's Homepage  Reply with Quote
Try this

select CONVERT(varchar(20),getdate(),103) + ' '+ LTRIM(STUFF(right(CONVERT(varchar(30), getdate(), 9), 14), 9, 4, ' '))

you can replace getdate() with the column where you are saving the time.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/24/2008 :  10:56:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT	PARSENAME(RIGHT(REPLACE(CONVERT(VARCHAR(26), GETDATE(), 100), ' ', '.'), 7), 1),
	PARSENAME(RIGHT(REPLACE(CONVERT(VARCHAR(26), GETDATE(), 109), ' ', '.'), 14), 1)



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/24/2008 :  11:01:20  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by snufse

Since I am using GridView and BoundFileds in my application, I have not had any sucess doing it there.



Bound fields are very easy to format if you return data with the proper type -- in this case, DateTime.

You can do all kinds of formatting with the DateFormatString property:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx

It's simple, it's easy, it's flexible, it's the way things are designed to be done.

Never, ever do this in T-SQL when you have a perfectly fine front-end application that can handle it.

*PLEASE* ignore the other advice given in this thread showing convoluted ways in T-SQL to convert things to VARCHARS.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 09/24/2008 11:02:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000