Convert time portion of datetime to string 1

[csteinhilber]

I have a legacy recordset that I'm trying to UNION with a newer recordset.

The legacy recordset has a time-based field with values such as "1899-12-30 13:30:00.0" (ie - ignore the date portion, pay attention to the time, 24-hour based).

Right or wrong, the newer recordset (upon which all the display layer code is based), stores it's times as strings such as "1:30PM" (12-hour based)... acceptable, since there will be no manipulation, just display.

Is there a simple way to convert the datetime "1899-12-30 13:30:00.0" to the varchar "1:30PM" so that I might UNION these two resultsets together?

Thanks in advance,
-Carl

 

[amrita418]

Try this:

select stuff(right(convert(varchar(26), convert(datetime, '1899-12-30 13:30:00.0'), 9), 14), 5, 7, '')

Regards,
AA

 

[amrita418]

Forgot that your field was already datetime, in which case you can get rid of the inner convert.

Regards,
AA

 

[csteinhilber]

Thanks Amrita!

That works great! Question is... HOW?
I've never used the STUFF() function before... and googling it doesn't help much ("stuff" is a waaaaay overused word in the English language, if you ask me ). I assume it's something akin to REPLACE(), but with control over positioning somehow?

But the real question is... where in that statement is it telling MSSQL that I want to convert the datetime to a 12-hour clock?

Like I said... it's working... but I'd really like to learn to fish on this one, if you have any other insight.

Thanks again!


-Carl

 

[SQLBill]

The CONVERT command changes the DATETIME value.

Refer to the BOL for more information about the different commands.

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Also, check out FAQ183-5834 and FAQ183-5842.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

STUFF
Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax
STUFF ( character_expression , start , length , character_expression )

It's listed in BOL under STUFF.

Hope that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[amrita418]

Yes you are right, it more or less works like replace.
From BOL:

STUFF
Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax
STUFF ( character_expression , start , length , character_expression )

In our example all we are doing replacing the unwanted part with ''

Converting the datetime to a 12-hour clock is done by the convert function (style 109).

You can break down the code and run each part to see how it works.

Regards,
AA

 

[SQLBill]

Another thing to try:

SELECT SUBSTRING((CONVERT(VARCHAR(19),GETDATE(),100)),13,7)

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Amrita418,

FYI: I was playing with your code and noticed something strange.

I ran the following:

Select GetDate()

select stuff(right(convert(varchar(26), GetDate(), 9), 14), 5, 7, '')

The result set I got was:

2005-06-30 12:47:08.450
and
12:40PM

The time did many strange things. I've hit 12:40 p.m. about 3 out of six times I've been running this, even though the GetDate() time is going forward, and I've gotten 12:43 and 12:47. At no time have I gotten the current time. And the last time I ran it, I got 7 minutes ahead of what time it was actually was.

I played with your code and changed the 5 to a 6 and it started behaving properly, returning the proper time.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[amrita418]

Oops, I guess I did not test it out well.

Thanks for the feed back.

Regards,
AA