Need to convert a date and time to a different format

[Google Jenny]

Precisely, here's what I need:

When I run getdate(), I get, for example:

August 9 2004 5:17 P.M.

I want to turn the date portion into:

8/9/2004 format and update one column with it
I want to turn 5:17 P.M. into:

hhmmss and update another column with it.
I've been playing around with datepart, with substr, with you name it,
and I'm stumped.

Any code samples, other help most appreciated.

Thanks,
Google Jenny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Bell]

Hi

If you column is a datetime data type then there will always be a time
portion. Not specifying the time when you update the field will default it
to 00:00:00. To return a string representation of the date and time you can
use the CONVERT function to specify a format. See the topic "CAST and
CONVERT" in books online. Use the REPLACE function if you want to strip out
the colons.

DECLARE @myDate datetime
SET @myDate = '20040809 17:17'

SELECT @myDate, CONVERT(char(10 ),@myDate,101) + ' ' +
REPLACE(CONVERT (char(8),@myDat e,108),':','')

John

"Google Jenny" <mi******@yahoo .com> wrote in message
news:41******** **************@ news.newsgroups .ws...

Precisely, here's what I need:

When I run getdate(), I get, for example:

August 9 2004 5:17 P.M.

I want to turn the date portion into:

8/9/2004 format and update one column with it
I want to turn 5:17 P.M. into:

hhmmss and update another column with it.
I've been playing around with datepart, with substr, with you name it,
and I'm stumped.

Any code samples, other help most appreciated.

Thanks,
Google Jenny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!