Cool & Smart SQL Date Format

Cool & Smart SQL Date Format

I have seen many guys wandering & goggling a lot to get their exact Date format in the SQL Query's.

ex:  SELECT convert(NVARCHAR, getdate(), 107) --> Oct 01, 2014

    SELECT convert(NVARCHAR, getdate(), 111) --> 2014/10/01

    SELECT convert(NVARCHAR, getdate(), 110) --> 10-01-2014

    SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/') --> 01/Oct/2014

    ....and so on.

After a similar struggle as above some times they luckily get or they struggle a lot to get & of course some time they might not get.

Even I was from that, after having a thought è

'Why not should we have some thing which easily gives me my Date format without remembering any thing?' 

with this thing I proceed and finally could able to get cool function written below which is very awesome & can be very  useful for every SQL developer.

-- =============================================
-- Taken from Blog : http://www.ilyasdotnetdeveloper.blogspot.com

-- Description: SQL DATES EASIEST CONVERTIONS

-- Example Usage: SELECT dbo.fnFormatDate (getdate(), 'DD-MON-YYYY')[MYDATE]

---==================================================

CREATE FUNCTION [dbo].[fnFormatDate] (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

AS

BEGIN

    DECLARE @StringDate VARCHAR(32)

    SET @StringDate = @FormatMask

    IF (CHARINDEX ('YYYY',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'YYYY',DATENAME(YY, @Datetime))

    IF (CHARINDEX ('YY',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY,  @Datetime),2))

    IF (CHARINDEX ('Month',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'Month', DATENAME(MM, @Datetime))

    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

        SET @StringDate = REPLACE(@StringDate, 'MON',LEFT(UPPER(DATENAME(MM,  @Datetime)),3))

    IF (CHARINDEX ('Mon',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @Datetime),3))

    IF (CHARINDEX ('MM',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'MM', RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

    IF (CHARINDEX ('M',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'M', CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

    IF (CHARINDEX ('DD',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'DD', RIGHT('0'+DATENAME(DD, @Datetime),2))

    IF (CHARINDEX ('D',@StringDate) > 0)

       SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime))  

RETURN @StringDate

END

PRESS F5 or Execute the function

Example usage ==>

SELECT dbo.fnFormatDate (getdate(), 'DD-MON-YYYY')[MYDATE] --01-OCT-2014

SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY')[MYDATE]  --01/10/2014

SELECT dbo.fnFormatDate (getdate(), 'DD,MONTH YYYY')[MYDATE]--01,October 2014

SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY')[MYDATE]  --10/01/201

..and more

I would suggest to save execute and save it after every SQL DB so that you can use any where.

I hope you enjoyed with the above stuff...