Is there a way in T-SQL to obtain the day of the month in a XXst, XXnd, XXrd, XXth notation?

• johnnycash

  Ten Centuries

  Points: 1172

  More actions

  February 10, 2011 at 3:59 pm

  #246518

  Hello everyone,

  I am wondering if there is already a function in SQL 2K5 that will return the day of the month in the XXst, XXnd, XXrd, XXth notation?

  I realize I could write a function to do so but if there is something there already, why try to re-invent the wheel, right?

  Thank you for all the help!

• johnnycash

  Ten Centuries

  Points: 1172

  More actions

  February 10, 2011 at 5:19 pm

  #1285533

  Well, it seems that I will have to write it myself. Here's the code for you to use and modify it to make more efficient, just let me know.

  Regards everyone!

  CREATE FUNCTION [dbo].[udf_DayOfTheMonth]

  (

  @date datetime = null

  )

  RETURNS varchar(4)

  AS

  BEGIN

  DECLARE @Result varchar(4)

  IF @Date IS NULL

  SET @Date = GETDATE()

  SELECT @Result =

  case when day(@date) > 9 then

  case

  when cast(substring(cast(day(@date) as varchar(2)),2,1)as int) = 1 then cast(day(@date) as varchar(2))+'st'

  when cast(substring(cast(day(@date) as varchar(2)),2,1)as int) = 2 then cast(day(@date) as varchar(2))+'nd'

  when cast(substring(cast(day(@date) as varchar(2)),2,1)as int) = 3 then cast(day(@date) as varchar(2))+'rd'

  ELSE

  cast(day(@date) as varchar(2))+'th'

  END

  ELSE

  case

  when day(@date) = 1 then cast(day(@date) as varchar(2))+'st'

  when day(@date) = 2 then cast(day(@date) as varchar(2))+'nd'

  when day(@date) = 3 then cast(day(@date) as varchar(2))+'rd'

  ELSE

  cast(day(@date) as varchar(2))+'th'

  END

  END

  RETURN @Result

  END

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  February 11, 2011 at 1:52 am

  #1285554

  Nothing wrong with your solution, but...

  Used in small numbers you may not notice anything, but the performance of UDFs that return a scalar value can be an issue when executed against a large dataset.

  Something like "SELECT dbo.udf_DayOfTheMonth(orderdate) FROM Orders" could take a long time if the Orders table was big.

  If you come up against this sort of performance issue with your solution, you may want to consider having a table with day and suffix that you can join to instead. You'd still have to get the day from your date first, and I'm not sure what the performance of the SQL Server's built in "day" function is like, so it may not help in the end.

  Just my 2p's worth.

• diamondgm

  SSCrazy

  Points: 2824

  More actions

  February 12, 2011 at 2:44 am

  #1286006

  Yeah, there doesnt seem to be a built-in way to produce ordinals.

  I haven't run you function, but the logic didnt check out in my head; I may be wrong but 11 may come out at '11st' and not '11th'?

  Good luck 🙂

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 12, 2011 at 12:57 pm

  #1286058

  diamondgm (2/12/2011)

  ---

  Yeah, there doesnt seem to be a built-in way to produce ordinals.

  I haven't run you function, but the logic didnt check out in my head; I may be wrong but 11 may come out at '11st' and not '11th'?

  Good luck 🙂

  diamondgm You are correct - got curious and modified the original posted by johnnycash. (Wanted to add it to my sandbox DB)

  It should function correctly with this:

  CREATE FUNCTION [dbo].[udf_DayOfTheMonth_05]

  --== Original code

  --==http://www.sqlservercentral.com/Forums/Topic1062357-338-1.aspx#bm1062958

  --== Author johnnycash

  (

  @date datetime = null

  )

  RETURNS varchar(4)

  AS

  BEGIN

  DECLARE @Result varchar(4)

  IF @Date IS NULL

  SET @Date = GETDATE()

  SELECT @Result =

  case when day(@date) > 9 then

  case

  --== Code added by Bitbucket

  when cast(substring(cast(day(@date) as varchar(2)),1,2)as int) = 11 then cast(day(@date) as varchar(2))+'th'

  when cast(substring(cast(day(@date) as varchar(2)),1,2)as int) = 12 then cast(day(@date) as varchar(2))+'th'

  when cast(substring(cast(day(@date) as varchar(2)),1,2)as int) = 13 then cast(day(@date) as varchar(2))+'th'

  when cast(substring(cast(day(@date) as varchar(2)),1,2)as int) = 31 then cast(day(@date) as varchar(2))+'st'

  --== End of code addition

  when cast(substring(cast(day(@date) as varchar(2)),2,2)as int) = 1 then cast(day(@date) as varchar(2))+'st'

  when cast(substring(cast(day(@date) as varchar(2)),2,2)as int) = 2 then cast(day(@date) as varchar(2))+'nd'

  when cast(substring(cast(day(@date) as varchar(2)),2,2)as int) = 3 then cast(day(@date) as varchar(2))+'rd'

  ELSE

  cast(day(@date) as varchar(2))+'th'

  END

  ELSE

  case

  when day(@date) = 1 then cast(day(@date) as varchar(2))+'xx'

  when day(@date) = 2 then cast(day(@date) as varchar(2))+'xx'

  when day(@date) = 3 then cast(day(@date) as varchar(2))+'xx'

  ELSE

  cast(day(@date) as varchar(2))+'xx'

  END

  END

  RETURN @Result

  END

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• johnnycash

  Ten Centuries

  Points: 1172

  More actions

  February 12, 2011 at 1:05 pm

  #1286061

  Thanks bitbucket and diamondgm.

  I did notice that yesterday as well, but since I need multi-language capabilities, I had to resort to building a table with each language that we use (EN, SP, IT, FR, DA, GE and CA-Catalan) and so I modified that SP that was calling this function to use the table instead directly.

  Thank you for all your feedback. Very positive and constructive!

  Regards,

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 12, 2011 at 7:08 pm

  #1286097

  This interested me, so I wrote a TVF for it....(single language though)

  CREATE FUNCTION OrdinalDateSuffix( @InputDate DATETIME )

  RETURNS TABLE

  AS

  RETURN ( WITH special(DAY,suffix)

  AS

  (

  SELECT1,'st' UNION ALL

  SELECT2,'nd' UNION ALL

  SELECT3,'rd' UNION ALL

  SELECT21,'st' UNION ALL

  SELECT22,'nd' UNION ALL

  SELECT23,'rd' UNION ALL

  SELECT31,'st'

  )

  SELECT

  CONVERT (NVARCHAR(2), DATEPART (DAY, @InputDate)) +

  COALESCE (

  (SELECT

  suffix

  FROM

  special

  WHERE

  DAY = DATEPART(DAY,@InputDate)

  ),'th') AS suffix

  )

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]