Date Function

• Minnu

  SSCertifiable

  Points: 6451

  More actions

  September 13, 2012 at 4:29 am

  #281532

  Hi,

  am using below funtion to convert date,

  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

  --

  Output is showing wrong for Dec

  declare @date varchar(100) = 'Dec 13, 2012'

  SELECT dbo.fnFormatDate (@date, 'Mon DD, YYYY')

  13ec 13, 2012

  Please help.............

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 13, 2012 at 4:35 am

  #1537119

  You don't need to use UDF, there is in-build format for T-SQL CONVERT function:

  select convert(varchar, GETDATE(), 107)

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]