Extract hh AM/PM from getdate()

Hi,
I am looking for a query to extract hour and AM or PM value from a date on sql2000.

ex/-
Input : 2001-12-28 22:18:07.810 (from getdate())
Output : 10 PM

select convert(varchar, (datepart(hh, convert(varchar, getdate(), 8)) % 12)) + ' ' +
substring (convert(varchar, convert(datetime, getdate(),20), 100),
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100)) - 1,
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100 )))

The above works but is there a better way to do this?This is a little shorter:

SELECT CONVERT(VARCHAR,DATEPART(hh,GETDATE())%12) +
CASE WHEN (DATEPART(hh,GETDATE())%12) > 0 THEN ' PM' ELSE ' AM' END|||thanks for your reply.
but i figured that 12 AM or 12 PM was displayed as 0 AM and 0 PM.
Hence to reduce my troubles, i will stick with the good ol' substring.

SELECT (substring(CONVERT(VARCHAR,getdate(),22),10,2) + ' ' +
substring(CONVERT(VARCHAR,getdate(),22), 19,2))

extended stored procedures: is there a 256 character limit on INPUT parameters?

Hello,
On SQL Server 2000 (SP3), for extended stored procedures: is there a
256 character limit on INPUT parameters?
And if so, is there a way around that?
Thanks,
BertAFAIK, there is no such limit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bert Szoghy" <webmaster@.quadmore.com> schrieb im Newsbeitrag
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Here's a quick test script for you:
CREATE PROCEDURE dbo.usp_Char256Test
@.s VARCHAR(257)
AS
PRINT LEN(@.s)
GO
DECLARE @.TempString VARCHAR(257)
SET @.TempString = REPLICATE('X', 257)
PRINT LEN(@.TempString)
EXEC dbo.usp_Char256Test @.TempString
This creates an SP that allows a 257 char VARCHAR to be passed in. It then
prints the Length of the passed in string to verify that all 257 chars were
passed successfully.
The limit you're encountering is probably due to a CHAR(256) or VARCHAR(256)
limit placed on the definition of the parameter in the SP declaration (above
I set the limit to 257, but any value up to 8000 for VARCHAR [4,000 for
NVARCHAR] should work).
"Bert Szoghy" <webmaster@.quadmore.com> wrote in message
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFTNGP14.phx.gbl>..
.
> Bert,
> For some system extended stored procedures, the parameters
> are defined as varchar(256). For those, there is no way around
> unless you rewrite the xp (not a good idea I think). For xp's you
> write yourself, I'm not aware of any limit. Are you having a
> particular problem you need help with?
> Steve Kass
> Drew University
> Bert Szoghy wrote:
>
Hello,
Thank you for your responses.
I have an extended stored proc DLL with C++ code which just looks fine
to me, which it refuses to give me more than 256 characters.
I found the following reference before posting here which made me
suspect it was a SQL Server limitation:
http://groups.google.ca/groups?hl=e...r />
GP15.phx.
gbl%26rnum%3D1
I am using a varchar(8000) in the trigger calling the extended stored
proc.
I'm about to try stepping through the DLL but the code is a bit nasty
and it would be nice if a SQL Server guru would point out a detail to
tweak.
Thanks guys!
Thanks again,
Bertsql