Returning Date Formats in Stored Procedures?

Ok, so you can use MONTH, DATENAME(mm) to return Month properties.
Using MONTH(...) returns the numeric value.
Using DATENAME(mm, ...) returns the full month name.
What if you wanted to get the Month returned to you formatted like:
Apr
Are you forced to return a substring of the return value, or is there
some special function?
On the same token, let's say the getdate() returns:
6/1/2006 11:00:37 AM
What if you wanted just:
Jun 1 11:00am
Note the removal of space afer time and removal of seconds, and
lowercase am/pm.For all intents and purposes, let's just use this ad-hoc Stored
Procedure for an example.
CREATE PROCEDURE pGetUploadRefs
AS
BEGIN
SELECT DATENAME(mm, DateTimeUploaded)
FROM SiteStatus
END
GO|||DECLARE @.dtSampleDt datetime, @.vchDay varchar(2), @.vchFormattedDt
varchar(15)
SELECT @.dtSampleDt = GETDATE() + 30 --FINAL OUTPUT: Jun 1 2:49pm
--SELECT @.dtSampleDt = DATEADD(mi, -167, GETDATE()) --FINAL OUTPUT: May
2 0:0pm
--SELECT @.dtSampleDt = DATEADD(mi, -888, GETDATE()) --FINAL OUTPUT: May
2 0:0am
SELECT @.vchDay = DATENAME(hh, @.dtSampleDt)
SELECT @.vchFormattedDt = LEFT(DATENAME(m, @.dtSampleDt), 3) + ' ' +
DATENAME(d, @.dtSampleDt) + ' '
SELECT @.dtSampleDt, @.vchDay, @.vchFormattedDt
IF (@.vchDay < 12)
BEGIN
SELECT @.vchFormattedDt = @.vchFormattedDt + DATENAME(hh, @.dtSampleDt) +
':' + DATENAME(mi, @.dtSampleDt) + 'am'
END
ELSE
BEGIN
SELECT @.vchFormattedDt = @.vchFormattedDt + CAST((DATEPART(hh,
@.dtSampleDt) - 12) AS VARCHAR) + ':' + DATENAME(mi, @.dtSampleDt) + 'pm'
END
SELECT @.vchFormattedDt
*** Sent via Developersdex http://www.examnotes.net ***|||The CONVERT function has an extra argument for formatting dates but I think
most people are going to tell you to do this client-side. Have a look at
CONVERT in BOL.
SELECT CONVERT ( VARCHAR, GETDATE(), 106 )
SELECT CONVERT ( VARCHAR, GETDATE(), 112 )
Potentially you could build your specially formatted string, but why do it
to yourself? You're going to get a performance hit breaking the strings up.
SELECT
CONVERT ( VARCHAR, GETDATE(), 106 ) + ' ' +
CAST( DATEPART( hour, GETDATE() ) AS VARCHAR ) + ':' +
CAST( DATEPART( minute, GETDATE() ) AS VARCHAR )
Doing it client-side, VBA / VB6 for example provides the Format function eg:
Format(Now(),"mmm d yyyy h:mmam/pm")
As flexible as you want!
You can easily format it in Excel, Crystal Reports etc. Is there a special
reason you need to do this?
Damien
"cider123@.hotmail.com" wrote:

> Ok, so you can use MONTH, DATENAME(mm) to return Month properties.
> Using MONTH(...) returns the numeric value.
> Using DATENAME(mm, ...) returns the full month name.
> What if you wanted to get the Month returned to you formatted like:
> Apr
> Are you forced to return a substring of the return value, or is there
> some special function?
> On the same token, let's say the getdate() returns:
> 6/1/2006 11:00:37 AM
> What if you wanted just:
> Jun 1 11:00am
> Note the removal of space afer time and removal of seconds, and
> lowercase am/pm.
>|||It's actually being interfaced in databound controls for ASP pages. I
want to be able to change the format (if desired by clients) on the fly
in a SP vs making changes in the web site.
I'm used to making such formats client side in code in the C# Apps I
develop, but my knowledge in the the ASP and SQL stuff is rather light.
I know how to do the basic functions and stored procedures, but
nothing really in depth like my current project has brought to the
table.
Thank you for all the help and feedback provided!