Creating a string from Date Fields

I have a table with a startdatetime and an enddatetime column such as:

StartDateTime EndDateTime what I want to see returned
is:
01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
1:30PM"
01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
10:30AM"

Maybe someone has accomplished this aready in a stored procedure and
has an example of how to do it?
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?

Looks like you need to use the following T-SQL functions/operators:

convert() - to format the date.
substring() - to extract the portions of the end time you want to display
CASE - to determine whether all of or just part of endtime is to be
included.

Then again, a lot of these display issuses are often best handled client
side.

The above-mentioned functions are all documented in Books Online, see
the T-SQL Reference. convert() may be tricky to find, as it is under
the topic CAST and CONVERT.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401291443.47b9d2c8@.posting.google.c om...
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?
> lq

You could do this with CONVERT() and various string functions, but it would
be better to use your client application to handle this. The dates above are
not correct for most European formats, for example, and it's much easier to
deal with client locale settings in a client-side application.

Simon