|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DateTime Formatting QuestionI have a datetime field that I'm storing entries for activities in, but I
need to retrieve the dates and times seperately (no problem there, Convert() saves the day for seperating them), but I need the Time in a 12 hour format instead of the 24 hour that SQL Server returns. Is there a way to do this in SQL (not in a stored procedure)? or do I need to figure out a programmatical method in my source code to convert it? Try,
declare @d datetime declare @s varchar(50) set @d = getdate() set @s = convert(varchar(50), @d, 9) select substring(@s, charindex(' ', reverse(@s)), 50) go AMB Show quote "Tom Andrecht" wrote: > I have a datetime field that I'm storing entries for activities in, but I > need to retrieve the dates and times seperately (no problem there, Convert() > saves the day for seperating them), but I need the Time in a 12 hour format > instead of the 24 hour that SQL Server returns. Is there a way to do this > in SQL (not in a stored procedure)? or do I need to figure out a > programmatical method in my source code to convert it? > > > With a little modification, that works perfectly. Thank You.
My Final SQL (since it's mostly static) is .... LTrim(substring(convert(varchar, tk_date, 0), charindex(' ', reverse(convert(varchar, tk_date, 0)), 9), 50)) as Time .... as this drops off the seconds and milliseconds (that we don't care about) and gives us the time formatted exactly the way it is needed. Thanks for the help. Tom Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:2D4C7C82-F4E1-4C54-A771-033DED89460A@microsoft.com... > Try, > > declare @d datetime > declare @s varchar(50) > > set @d = getdate() > set @s = convert(varchar(50), @d, 9) > > select substring(@s, charindex(' ', reverse(@s)), 50) > go > > > AMB > > > "Tom Andrecht" wrote: > >> I have a datetime field that I'm storing entries for activities in, but I >> need to retrieve the dates and times seperately (no problem there, >> Convert() >> saves the day for seperating them), but I need the Time in a 12 hour >> format >> instead of the 24 hour that SQL Server returns. Is there a way to do >> this >> in SQL (not in a stored procedure)? or do I need to figure out a >> programmatical method in my source code to convert it? >> >> >> This may work for you, and perhaps be more 'understandable' for junior staff
that will have to maintain the project. SELECT substring( (convert(varchar(30), getdate(), 109)), 13, 14 ) -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Tom Andrecht" <tom.andre***@dmacorporation.com> wrote in message news:uERlUEPrGHA.4812@TK2MSFTNGP04.phx.gbl... >I have a datetime field that I'm storing entries for activities in, but I >need to retrieve the dates and times seperately (no problem there, >Convert() saves the day for seperating them), but I need the Time in a 12 >hour format instead of the 24 hour that SQL Server returns. Is there a way >to do this in SQL (not in a stored procedure)? or do I need to figure out a >programmatical method in my source code to convert it? > It might work for storing the time, but I'm only interested in retrieving
it. Thanks anyway. Tom Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:eNmR5qPrGHA.4324@TK2MSFTNGP03.phx.gbl... > This may work for you, and perhaps be more 'understandable' for junior > staff that will have to maintain the project. > > SELECT substring( (convert(varchar(30), getdate(), 109)), 13, 14 ) > > > > -- > Arnie Rowland > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Tom Andrecht" <tom.andre***@dmacorporation.com> wrote in message > news:uERlUEPrGHA.4812@TK2MSFTNGP04.phx.gbl... >>I have a datetime field that I'm storing entries for activities in, but I >>need to retrieve the dates and times seperately (no problem there, >>Convert() saves the day for seperating them), but I need the Time in a 12 >>hour format instead of the 24 hour that SQL Server returns. Is there a >>way to do this in SQL (not in a stored procedure)? or do I need to figure >>out a programmatical method in my source code to convert it? >> > > Help me understand what part of my suggestion didn't seem as though it was
useful for retrieval? I assumed that you would understand how to replace getdate() with the field name from your table -was that a bad assumption?. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Tom Andrecht" <tom.andrecht@nospam.dmacorporation.com> wrote in message news:%23NJ6s9PrGHA.2440@TK2MSFTNGP03.phx.gbl... > It might work for storing the time, but I'm only interested in retrieving > it. > Thanks anyway. > > Tom > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:eNmR5qPrGHA.4324@TK2MSFTNGP03.phx.gbl... >> This may work for you, and perhaps be more 'understandable' for junior >> staff that will have to maintain the project. >> >> SELECT substring( (convert(varchar(30), getdate(), 109)), 13, 14 ) >> >> >> >> -- >> Arnie Rowland >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Tom Andrecht" <tom.andre***@dmacorporation.com> wrote in message >> news:uERlUEPrGHA.4812@TK2MSFTNGP04.phx.gbl... >>>I have a datetime field that I'm storing entries for activities in, but I >>>need to retrieve the dates and times seperately (no problem there, >>>Convert() saves the day for seperating them), but I need the Time in a 12 >>>hour format instead of the 24 hour that SQL Server returns. Is there a >>>way to do this in SQL (not in a stored procedure)? or do I need to figure >>>out a programmatical method in my source code to convert it? >>> >> >> > > It was a bad assumption, though no fault of yours. my brain is fried. I
was thinking too literally. Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:%23y7EHWQrGHA.2052@TK2MSFTNGP02.phx.gbl... > Help me understand what part of my suggestion didn't seem as though it was > useful for retrieval? > > I assumed that you would understand how to replace getdate() with the > field name from your table -was that a bad assumption?. > > -- > Arnie Rowland > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Tom Andrecht" <tom.andrecht@nospam.dmacorporation.com> wrote in message > news:%23NJ6s9PrGHA.2440@TK2MSFTNGP03.phx.gbl... >> It might work for storing the time, but I'm only interested in retrieving >> it. >> Thanks anyway. >> >> Tom >> >> "Arnie Rowland" <ar***@1568.com> wrote in message >> news:eNmR5qPrGHA.4324@TK2MSFTNGP03.phx.gbl... >>> This may work for you, and perhaps be more 'understandable' for junior >>> staff that will have to maintain the project. >>> >>> SELECT substring( (convert(varchar(30), getdate(), 109)), 13, 14 ) >>> >>> >>> >>> -- >>> Arnie Rowland >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> >>> "Tom Andrecht" <tom.andre***@dmacorporation.com> wrote in message >>> news:uERlUEPrGHA.4812@TK2MSFTNGP04.phx.gbl... >>>>I have a datetime field that I'm storing entries for activities in, but >>>>I need to retrieve the dates and times seperately (no problem there, >>>>Convert() saves the day for seperating them), but I need the Time in a >>>>12 hour format instead of the 24 hour that SQL Server returns. Is there >>>>a way to do this in SQL (not in a stored procedure)? or do I need to >>>>figure out a programmatical method in my source code to convert it? >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics