Convert function

In a query, how do I convert a datetime to display as mm/dd/yy hh:mm AMorPM?
I don't see that option on the list of values in Sql Books. The only option
I find for including the time abbreviates the month rather than using
numericals:
Convert(varchar, myDateTime, 9) displays: Jun 17 1998 12:00:00:000AM
where as I want it to display 06/17/98 12:00 AM
Same for using numbers that you want it to always display decimals.
Convert(varchar, myNumber, 1) displays 7.1750000e+002
whereas I want it to display 7.17, or I want 7.014 to display as 7.01 and I
want 7.1 to display as 7.10convert(VarChar(10), myDateTime, 1) + ' ' +
Right(convert(VarChar(26), myDateTime, 9), 14)
And for Numbers, Check out the Str() FUnction Takes 3 Arguments,
Str(val, N, M)
val is expression evaluates to a number (integral or floating)
N Is Number of digits to Display,
M is number of digits to right of decimal point to round off to...
"Tory" wrote:

> In a query, how do I convert a datetime to display as mm/dd/yy hh:mm AMorP
M?
> I don't see that option on the list of values in Sql Books. The only opti
on
> I find for including the time abbreviates the month rather than using
> numericals:
> Convert(varchar, myDateTime, 9) displays: Jun 17 1998 12:00:00:000AM
> where as I want it to display 06/17/98 12:00 AM
> Same for using numbers that you want it to always display decimals.
> Convert(varchar, myNumber, 1) displays 7.1750000e+002
> whereas I want it to display 7.17, or I want 7.014 to display as 7.01 and
I
> want 7.1 to display as 7.10
>
>|||The convert for the date works great! For the number, however, I don't know
the "N" as it may be 3 characters (123) or it may be 5 characters (1234.5).
Also I want the number to always display with 2 digits to the right, for
instance 123 would be returned as 123.00
Thanks.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F0CE3A77-FF02-40D6-AC58-B186D897B38A@.microsoft.com...
> convert(VarChar(10), myDateTime, 1) + ' ' +
> Right(convert(VarChar(26), myDateTime, 9), 14)
> And for Numbers, Check out the Str() FUnction Takes 3 Arguments,
> Str(val, N, M)
> val is expression evaluates to a number (integral or floating)
> N Is Number of digits to Display,
> M is number of digits to right of decimal point to round off to...
> "Tory" wrote:
>
AMorPM?
option
and I|||The N Just has to be big "enough" If you are outputing an integer, for
example, it can only go to 2 Billion, so N = 9 + hpwever many decimal places
will suffice... Str Fucntion will add extra spaces up to the value of N
Try this:
Select Str(123.5678912345, 12, 4) to see what I mean...
If you don't want the extra spaces, Use Ltrim() Function to strip them off..
Select Str(123.5678912345, 12, 4), LTrim(Str(123.5678912345, 12, 4))
"Tory" wrote:

> The convert for the date works great! For the number, however, I don't kn
ow
> the "N" as it may be 3 characters (123) or it may be 5 characters (1234.5)
.
> Also I want the number to always display with 2 digits to the right, for
> instance 123 would be returned as 123.00
> 8
> Thanks.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F0CE3A77-FF02-40D6-AC58-B186D897B38A@.microsoft.com...
> AMorPM?
> option
> and I
>
>|||While you have that "code museum" proprietary CONVERT (), the basic
principle of a tiered architecture is that you do frmatting and display
in the front end and never in the database. Standard SQL has one and
only one date format, based on ISO-8601. Each X3J host language
Standard has conversion rules for SQL data types.
You are still thinking in terms of a 3GL, and not SQL. In a 3GL, the
files and the apps were integrated, monolithic. In a tiered
architecture, each tier does one and only one task.|||Got it! Thanks so much, that works perfectly!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:AA1384B2-F109-44C3-8F32-B7D7A8DDBFAA@.microsoft.com...
> The N Just has to be big "enough" If you are outputing an integer, for
> example, it can only go to 2 Billion, so N = 9 + hpwever many decimal
places
> will suffice... Str Fucntion will add extra spaces up to the value of N
> Try this:
> Select Str(123.5678912345, 12, 4) to see what I mean...
> If you don't want the extra spaces, Use Ltrim() Function to strip them
off..
> Select Str(123.5678912345, 12, 4), LTrim(Str(123.5678912345, 12, 4))
> "Tory" wrote:
>
know
(1234.5).
only
using
12:00:00:000AM
7.01