|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Displaying time as 1:20 PMI 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? Here is one option:
SELECT stuff( ( right( convert( varchar(30), getdate(), 100 ), 7 )), 6, 0, ' ' ) -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "jonefer" <jone***@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? > That doesn't sort as time - it sorts as string
Show quote "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" <jone***@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 jonefer wrote: Show quote > 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" <jone***@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. Show quote "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" <jone***@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 (jone***@discussions.microsoft.com) writes:
> yep, that's what I ended up doing - however, I was using a 'SELECT Sure, display it in 24 hour format, and make sure there is a leading space> 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. 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, esq***@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 http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 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 jonefer wrote: Show quote >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" <jone***@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. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "jonefer" <jone***@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" <jone***@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. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "jonefer" <jone***@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" <jone***@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? >> >  
Other interesting topics
|
|||||||||||||||||||||||
