Resources » Code Snippets » SQL

SQL UDF: Convert Date Format CCYYMMDD to MMDDYYYY/MMDDYY


Posted Date: 13-Jun-2009  Last Updated:   Category: SQL    
Author: Member Level: Gold    Points: 10



The following SQL user defined function is used to convert the given date in CCYYMMDD or CCYY/MM/DD format to MM/DD/CCYY.

/*This function returns mm/dd/ccyy given a date in the format ccyymmdd */
/* Input of CCYYMMDD or CCYY/MM/DD will output MM/DD/CCYY */
/* dbo. FuncCCYYMMDD_MM_DD_CCYY(FieldName) */
/* Example: 19780621 = 06/21/1978*/





CREATE FUNCTION dbo.FuncCCYYMMDD_MM_DD_CCYY (@Date varChar(50)) RETURNS varchar(50)
AS
Begin
Declare @Result as varchar(50)
SET @Result = substring(@Date,5,2) + '/' + Substring(@Date,7,2) + '/' + Substring(@Date,1,4)
IF @Result = '00/00/0000'
Set @Result = Null
Return (@Result)
End





This SQL user defined function is used to convert the given date in the format CCYYMMDD or CCYY/MM/DD into the format MM/DD/YY.


/*This function returns mm/dd/yy given a date in the format ccyymmdd */
/* Input of CCYYMMDD or CCYY/MM/DD will output MM/DD/YY */
/* dbo. FuncCCYYMMDD_MM_DD_YY(FieldName) */
/* Example: 19780621 = 06/21/78*/





CREATE FUNCTION dbo.FuncCCYYMMDD_MM_DD_YY (@Date VARCHAR(50)) RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Result AS VARCHAR(50)
SET @Result = substring(@Date,5,2) + '/' + Substring(@Date,7,2) + '/' + Substring(@Date,3,2)
IF @Result = '00/00/0000'
SET @Result = NULL
RETURN (@Result)
END



Did you like this resource? Share it with your friends and show your love!

Responses to "SQL UDF: Convert Date Format CCYYMMDD to MMDDYYYY/MMDDYY"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Ramya
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India