DateTime column conversion

1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ?
For example:
The column dTime is of DateTime datatype and has the value of 12:26:05 AM.
When I do the following sql:
select CONVERT( CHAR(10), dTime, 8) from myTbl
it returns 00:26:13 instead of 12:26:13 AM
2. How can I convert DateTime datatype column to dd-mon-yy ?
For example:
The column dDay is of DateTime datatype and has the value of 11/29/2005.
When I do the following sql:
select CONVERT( CHAR(10), dDay, 6) from myTbl
it returns 29 Nov 05 instead of 29-Nov-05
Thank you.> 1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ?
SELECT LTRIM(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11))
Or, format it where it belongs, in the presentation layer.

> 2. How can I convert DateTime datatype column to dd-mon-yy ?
SELECT REPLACE(CONVERT(CHAR(9), GETDATE(), 6), ' ', '-')
Or, format it where it belongs, in the presentation layer.|||fniles wrote:

> 1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ?
> For example:
> The column dTime is of DateTime datatype and has the value of 12:26:05 AM.
> When I do the following sql:
> select CONVERT( CHAR(10), dTime, 8) from myTbl
> it returns 00:26:13 instead of 12:26:13 AM
> 2. How can I convert DateTime datatype column to dd-mon-yy ?
> For example:
> The column dDay is of DateTime datatype and has the value of 11/29/2005.
> When I do the following sql:
> select CONVERT( CHAR(10), dDay, 6) from myTbl
> it returns 29 Nov 05 instead of 29-Nov-05
> Thank you.
The smartest answer is to format this stuff client side. After all,
some of your users may have a legitimate need to cut-and-paste or sort
the dates in some external app. That's hard to do if you return a
clumsy and ambiguous string format such as "29-Nov-05". Also, maybe not
every user's preferred language will be English.
If you are the only user or if you prefer to dictate to your users how
they should read dates and times, try these:
SELECT SUBSTRING(CONVERT(CHAR(26),CURRENT_TIMES
TAMP,9),13,8)
+ RIGHT(CONVERT(CHAR(26),CURRENT_TIMESTAMP
,9),2) ;
SELECT REPLACE(CONVERT(CHAR(10),CURRENT_TIMESTA
MP,6),' ','-') ;
David Portas
SQL Server MVP
--|||Thank you for your help.
Is it correct that "select CONVERT(CHAR(20), GETDATE(), 22) " return
something like "12/30/99 12:25:13 AM" ?
Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wSsGoMBGHA.2576@.TK2MSFTNGP10.phx.gbl...
> SELECT LTRIM(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11))
> Or, format it where it belongs, in the presentation layer.
>
> SELECT REPLACE(CONVERT(CHAR(9), GETDATE(), 6), ' ', '-')
> Or, format it where it belongs, in the presentation layer.
>|||> Is it correct that "select CONVERT(CHAR(20), GETDATE(), 22) " return
> something like "12/30/99 12:25:13 AM" ?
Did you try it?|||Yes, and it shows "12/30/99 1", which to me looks truncated, because when I
did LTRIM(RIGHT(CONVERT(CHAR(20), HistTradesOrig.filltime, 22), 20)), it
returns "12/30/99 12:25:13 AM"
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OmbQjwMBGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Did you try it?
>|||That looks like CONVERT(CHAR(10), not CONVERT(CHAR(20) ... otherwise, can
you show a repro?
"fniles" <fniles@.pfmail.com> wrote in message
news:emaa2%23MBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Yes, and it shows "12/30/99 1", which to me looks truncated, because when
> I did LTRIM(RIGHT(CONVERT(CHAR(20), HistTradesOrig.filltime, 22), 20)), it
> returns "12/30/99 12:25:13 AM"
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OmbQjwMBGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||Yes, you are correct, my mistake. Sorry about that.
Thank you for your help.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239zphBNBGHA.3916@.tk2msftngp13.phx.gbl...
> That looks like CONVERT(CHAR(10), not CONVERT(CHAR(20) ... otherwise, can
> you show a repro?
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:emaa2%23MBGHA.216@.TK2MSFTNGP15.phx.gbl...
>