Convert

I am using the following snippet of code to help me convert the date and tim
e
in a query I am writing.
SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
dbo.Subjects.Subject, Convert
(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
The date converts fine to the format that I need. The time does not. It is
being displayed as military times and I want it to appear as a standard 12
format. i.e 9:00 AM
I tried all of the codes I found in BOL and none gave me what I wanted. Is
it possible to do what I want to do?
ThanksI think you can use the following:
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
HTH
Barry|||Thanks. But it still shows up as military time when I use 100.
"Barry" wrote:

> I think you can use the following:
> Convert
> (Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
> HTH
> Barry
>|||Umm not sure why - I have just checked the BOL and it confirms my
suggestion in the CAST and CONVERT section.
What are storing the Time as? Datetime?
Barry|||right(convert(varchar,dbo.TrainingSchedule.RequestedTime,100),7) as Time
Brennan wrote:

>I am using the following snippet of code to help me convert the date and ti
me
>in a query I am writing.
>SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
>dbo.Subjects.Subject, Convert
>(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
> Convert
>(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
>The date converts fine to the format that I need. The time does not. It i
s
>being displayed as military times and I want it to appear as a standard 12
>format. i.e 9:00 AM
>I tried all of the codes I found in BOL and none gave me what I wanted. Is
>it possible to do what I want to do?
>Thanks
>|||Usually, formatting is best left to the client since most client languages
have far better capabilities in this area. It isn't particularly clear what
datatypes you are using for the columns in question - the assumption is that
they are both datetime (or smalldatetime). If this assumption is not valid,
then you should clarify what the datatypes are and the expected formats of
the data (if applicable). One can question the wisdom of separating these
two intimately related bits of information into two separate columns -
especially given the dbms support.
If you must persist in this quest, you will most likely need to "generate"
the appropriate information in some convoluted and complex expression (and
possibly multiple queries). For the convert function, none of the available
formats has a space between the time and the AM/PM characters. If this can
be ignored, the 100 format is the closest - convert to this format and take
the last 7 characters (or all the characters from the last space to the end
of the string). You could also use the datepart functions to strip off and
convert the bits that are of interest. Experiment a bit - I think you will
understand the reason for the my first statement.|||Can you post a repro? Is the datatype really datetime? Also, I agree that fo
rmatting is best
performed in the client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:753F4CC7-DC7F-496E-8834-51667F2BFD0B@.microsoft.com...
> Thanks. But it still shows up as military time when I use 100.
> "Barry" wrote:
>|||Thanks I agree with you about the client. I am using smalldatetime.
My problem is that my client is a DNN portal. I am using an add in module
that let's me dynamically display the results of an SQL statement in a grid
on any selected page.
Unfortunately, it does not give me the opportunity to adjust any formatting
which was why I was trying to approach it from a Convert perspective. And I
know nothing about asp so I can't approach the problem from the client side.
I'll try some of the solutions mentions here, but I think I'm going to end u
p
writing an RS report to provide this information to our end users.
Thanks
"Scott Morris" wrote:

> Usually, formatting is best left to the client since most client languages
> have far better capabilities in this area. It isn't particularly clear wh
at
> datatypes you are using for the columns in question - the assumption is th
at
> they are both datetime (or smalldatetime). If this assumption is not vali
d,
> then you should clarify what the datatypes are and the expected formats of
> the data (if applicable). One can question the wisdom of separating these
> two intimately related bits of information into two separate columns -
> especially given the dbms support.
> If you must persist in this quest, you will most likely need to "generate"
> the appropriate information in some convoluted and complex expression (and
> possibly multiple queries). For the convert function, none of the availab
le
> formats has a space between the time and the AM/PM characters. If this ca
n
> be ignored, the 100 format is the closest - convert to this format and tak
e
> the last 7 characters (or all the characters from the last space to the en
d
> of the string). You could also use the datepart functions to strip off an
d
> convert the bits that are of interest. Experiment a bit - I think you wil
l
> understand the reason for the my first statement.
>
>