Resources » Code Snippets » SQL

SQL UDF: Convert Date Format CCYYMMDD to MMDDYYYY/MMDDYY


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
    Today
      Last 7 Daysmore...

      Awards & Gifts
      Talk to Webmaster Tony John

      Online Members

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