|
|
You're in the
sql server programming
group:Displaying time as 1:20 PM
sql server programming:
cast(convert(char(12),TimeOfAppt,8) as datetime) as Time What i'm really trying to do is display it as: 1:20 PM this must be easy?
Here is one option:
SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, = 0, ' ' ) --=20 Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience.=20 Most experience comes from bad judgment.=20 - Anonymous [quoted text, click to view] "jonefer" <jonefer@discussions.microsoft.com> wrote in message =
news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... >I managed to get my char text into a format that can now be sorted: > cast(convert(char(12),TimeOfAppt,8) as datetime) as Time >=20 > What i'm really trying to do is display it as: > 1:20 PM >=20 > this must be easy?
That doesn't sort as time - it sorts as string
[quoted text, click to view] "Arnie Rowland" wrote:
> Here is one option: > > SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, 0, ' ' ) > > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "jonefer" <jonefer@discussions.microsoft.com> wrote in message news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... > >I managed to get my char text into a format that can now be sorted: > > cast(convert(char(12),TimeOfAppt,8) as datetime) as Time > > > > What i'm really trying to do is display it as: > > 1:20 PM > > > > this must be easy?
Your display doesn't have to match your sorting criteria; however, in
most cases, it's a good idea to allow your application (not your database) to handle your formatting. However, if you are using a very thin application layer, you could do something like (using Arnie's example): SELECT stuff( ( right( convert( varchar(30), datecolumn, 100 ), 7 )), 6, 0, ' ' ) FROM Table ORDER BY datecolumn Stu SELECT [quoted text, click to view] jonefer wrote:
> That doesn't sort as time - it sorts as string > > "Arnie Rowland" wrote: > > > Here is one option: > > > > SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, 0, ' ' ) > > > > > > -- > > Arnie Rowland, Ph.D. > > Westwood Consulting, Inc > > > > Most good judgment comes from experience. > > Most experience comes from bad judgment. > > - Anonymous > > > > > > "jonefer" <jonefer@discussions.microsoft.com> wrote in message news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... > > >I managed to get my char text into a format that can now be sorted: > > > cast(convert(char(12),TimeOfAppt,8) as datetime) as Time > > > > > > What i'm really trying to do is display it as: > > > 1:20 PM > > > > > > this must be easy? > > >
yep, that's what I ended up doing - however, I was using a 'SELECT DISTINCT'
which conflicts, so I ended up making a view that was grouped. Seems kind of a-round-about way just to show 1:20 PM that can be sorted. --- there must be an easier way to show the time in a way that can be sorted. [quoted text, click to view] "Stu" wrote:
> Your display doesn't have to match your sorting criteria; however, in > most cases, it's a good idea to allow your application (not your > database) to handle your formatting. However, if you are using a very > thin application layer, you could do something like (using Arnie's > example): > > SELECT stuff( ( right( convert( varchar(30), datecolumn, 100 ), 7 )), > 6, 0, ' ' ) > FROM Table > ORDER BY datecolumn > > Stu > > SELECT > jonefer wrote: > > That doesn't sort as time - it sorts as string > > > > "Arnie Rowland" wrote: > > > > > Here is one option: > > > > > > SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, 0, ' ' ) > > > > > > > > > -- > > > Arnie Rowland, Ph.D. > > > Westwood Consulting, Inc > > > > > > Most good judgment comes from experience. > > > Most experience comes from bad judgment. > > > - Anonymous > > > > > > > > > "jonefer" <jonefer@discussions.microsoft.com> wrote in message news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... > > > >I managed to get my char text into a format that can now be sorted: > > > > cast(convert(char(12),TimeOfAppt,8) as datetime) as Time > > > > > > > > What i'm really trying to do is display it as: > > > > 1:20 PM > > > > > > > > this must be easy? > > > > >
I was responding to your post where you wrote: "i'm really trying to do is
display it" If you are concerned about ORDER BY behavior, that is something quite different from DISPLAY behavior. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous [quoted text, click to view] "jonefer" <jonefer@discussions.microsoft.com> wrote in message news:BE7521BE-AB3D-406C-B1BE-608CCAADFFFA@microsoft.com... > That doesn't sort as time - it sorts as string > > "Arnie Rowland" wrote: > >> Here is one option: >> >> SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, >> 0, ' ' ) >> >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "jonefer" <jonefer@discussions.microsoft.com> wrote in message >> news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... >> >I managed to get my char text into a format that can now be sorted: >> > cast(convert(char(12),TimeOfAppt,8) as datetime) as Time >> > >> > What i'm really trying to do is display it as: >> > 1:20 PM >> > >> > this must be easy? >> >
None of the datatypes in SQL Server has any implied formatting. The client application formats the
value for you. If you want to define some particular format when the data leave SQL Server, you will have to convert to a string, where things like ordering can be a problem. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "jonefer" <jonefer@discussions.microsoft.com> wrote in message
news:BE7521BE-AB3D-406C-B1BE-608CCAADFFFA@microsoft.com... > That doesn't sort as time - it sorts as string > > "Arnie Rowland" wrote: > >> Here is one option: >> >> SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, 0, ' ' ) >> >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "jonefer" <jonefer@discussions.microsoft.com> wrote in message >> news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... >> >I managed to get my char text into a format that can now be sorted: >> > cast(convert(char(12),TimeOfAppt,8) as datetime) as Time >> > >> > What i'm really trying to do is display it as: >> > 1:20 PM >> > >> > this must be easy? >> >
jonefer (jonefer@discussions.microsoft.com) writes:
[quoted text, click to view] > yep, that's what I ended up doing - however, I was using a 'SELECT > DISTINCT' which conflicts, so I ended up making a view that was grouped. > Seems kind of a-round-about way just to show 1:20 PM that can be > sorted. > > --- there must be an easier way to show the time in a way that can be > sorted. Sure, display it in 24 hour format, and make sure there is a leading space before 10 o'clock. But the best is of course to leave format to the client. The client can apply the regional settings and if the users prefers, he can see the time displayed as 13:20. If you really insist on formatting the time in SQL Server, 24-hour is the only reasonable option. AM/PM can just lead to confusion for people who are not accustomed to that notation. (Hey, even for people who are it, ic can go wrong. I've seen more than one slide when I've been to conferences in the US where it has said a.m. when it should have said p.m. or vice versa. That would never happen here!) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
As others have suggested, time is time and strings are strings,
and you can't have a "time" that "shows" in a specific format - it would be a string not a time. One way to handle this is to be explicit about the fact that time and strings are different, and perhaps this is what you ultimately did: select datedisplay, other_columns from ( select distinct datecolumn, <messy string expression> as datedisplay, other_columns from T ) as T_plus_datedisplay order by datecolumn It's not really roundabout if you think of it as outputting something different than what you order by. -- Steve Kass -- Drew University -- http://www.stevekass.com [quoted text, click to view] jonefer wrote:
>yep, that's what I ended up doing - however, I was using a 'SELECT DISTINCT' >which conflicts, so I ended up making a view that was grouped. Seems kind of >a-round-about way just to show 1:20 PM that can be sorted. > >--- there must be an easier way to show the time in a way that can be sorted. > >"Stu" wrote: > > > >>Your display doesn't have to match your sorting criteria; however, in >>most cases, it's a good idea to allow your application (not your >>database) to handle your formatting. However, if you are using a very >>thin application layer, you could do something like (using Arnie's >>example): >> >>SELECT stuff( ( right( convert( varchar(30), datecolumn, 100 ), 7 )), >>6, 0, ' ' ) >>FROM Table >>ORDER BY datecolumn >> >>Stu >> >>SELECT >>jonefer wrote: >> >> >>>That doesn't sort as time - it sorts as string >>> >>>"Arnie Rowland" wrote: >>> >>> >>> >>>>Here is one option: >>>> >>>>SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, 0, ' ' ) >>>> >>>> >>>>-- >>>>Arnie Rowland, Ph.D. >>>>Westwood Consulting, Inc >>>> >>>>Most good judgment comes from experience. >>>>Most experience comes from bad judgment. >>>>- Anonymous >>>> >>>> >>>>"jonefer" <jonefer@discussions.microsoft.com> wrote in message news:BAFC4E3F-A3DE-48D6-ABBF-89366F73C4FF@microsoft.com... >>>> >>>> >>>>>I managed to get my char text into a format that can now be sorted: >>>>>cast(convert(char(12),TimeOfAppt,8) as datetime) as Time >>>>> >>>>>What i'm really trying to do is display it as: >>>>>1:20 PM >>>>> >>>>>this must be easy? >>>>> >>>>> >>>>> >>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |