nVarchar Conversion - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

nVarchar Conversion

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2011-08-02 : 11:04:46

I've nvarchar field with time data in it like
Table Name : ResultMast
Field Name : lTime
Data :
11:00
11:30
12:00
12:30
13:00
13:30

I've to show this time in 12 hour format with AM PM Clause like

11:00 AM
11:30 AM
12:00 AM
12:30 PM

01:00 PM
01:30 PM

How can I???

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 11:16:32

[code]
Declare @t table (
id int identity(1,1),
tm nvarchar(10)
)

Insert Into @t Select '11:00'
Insert Into @t Select '11:30'
Insert Into @t Select '12:00'
Insert Into @t Select '12:30'
Insert Into @t Select '13:00'
Insert Into @t Select '13:30'

Select
id,
tm,
right('0'+convert(varchar,CONVERT(time,tm),0),7)
From @t
[/code]

Corey

I Has Returned!!

ravininave
Posting Yak Master

111 Posts

Posted - 2011-08-02 : 11:52:46

quote:

Originally posted by Seventhnight


Declare @t table (
	id int identity(1,1),
	tm nvarchar(10)
)

Insert Into @t Select '11:00'
Insert Into @t Select '11:30'
Insert Into @t Select '12:00'
Insert Into @t Select '12:30'
Insert Into @t Select '13:00'
Insert Into @t Select '13:30'


Select 
	id,
	tm,
	right('0'+convert(varchar,CONVERT(time,tm),0),7)
From @t

Corey

I Has Returned!!

Oh Gr8. It's working absolutely fine. But can u plz suggest me how to order it when i add order by tm then it shows me 1:00pm first
Thanx again

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-02 : 14:34:40

[code]
Select
id,
tm,
right('0'+convert(varchar,CONVERT(time,tm),0),7)
From @t
Order By CONVERT(time,tm)
[/code]

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources