{"id":334,"date":"2011-06-24T12:00:00","date_gmt":"2011-06-24T12:00:00","guid":{"rendered":"http:\/\/www.greytrix.com\/blogs\/sagecrm\/?p=334"},"modified":"2011-06-24T12:00:00","modified_gmt":"2011-06-24T12:00:00","slug":"date-format-with-suffix","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/","title":{"rendered":"Date Format with Suffix"},"content":{"rendered":"

Date formats in Sage CRM always seem to be an area of discussion. A Date is made of three elements namely Month, Date and Year and you can have various standard combinations depending on country to have a date format derived out of these three basic elements.<\/p>\n

<\/p>\n

While it is easy to interpret a DD\/MM\/YY or MM\/DD\/YYYY formats technically, recently I came across a slightly challenging scenario where a suffix was included along with the date, for example \u201c31st March 2011\u201d which is usually used in printed documents rather than being stored in database in this format.<\/p>\n

<\/p>\n

Initially it seemed to be tricky to extract date from this data as there are other suffix possibilities like \u201cnd\u201d,\u201drd\u201d and \u201cth\u201d. Instead of using the regular javascript to incorporate suffix I tried my hands on writing a SQL server function which can parse this type of input data and give you date in format liked by your program; I am sharing this function; although I was working on Sage CRM customization but this is generic SQL server function which can be used in any SQL based Application.<\/p>\n

<\/p>\n

\nQuery<\/legend>\n

create function [dbo].[get_date_suffix](
@date datetime = null
)
returns nvarchar(50)
as begin
declare @d int,
@m nvarchar(15),
@y nvarchar(4),
@end nvarchar(1),
@return nvarchar(50)<\/p>\n

if @date is null
set @date=getdate()
select @d=datepart(d, @date), @m=datename(m, @date), @y=datename(yyyy,@date), @end=right(convert(nvarchar(2), @d),1)
set @return= convert(nvarchar(2), @d)
+case
when @d in(11, 12, 13) then ‘th’
when @end=’1′ then ‘st’
when @end=’2′ then ‘nd’
when @end=’3′ then ‘rd’
else ‘th’
end
+’ ‘+@m+’ ‘+@y
return @return
end<\/fieldset>\n

If you find this content useful, please drop us an email at crm@greytrix.com<\/a>. <\/b><\/marquee><\/p>\n","protected":false},"excerpt":{"rendered":"

Date formats in Sage CRM always seem to be an area of discussion. A Date is made of three elements namely Month, Date and Year and you can have various standard combinations depending on country to have a date format derived out of these three basic elements. While it is easy to interpret a DD\/MM\/YY\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[332],"tags":[924,927,928,2030,2031,2325],"class_list":["post-334","post","type-post","status-publish","format-standard","hentry","category-sage-crm","tag-date","tag-dateformat","tag-datetime","tag-sage-crm-7-0","tag-sage-crm-7-1","tag-sql"],"yoast_head":"\nDate Format with Suffix - Sage CRM \u2013 Tips, Tricks and Components<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Date Format with Suffix - Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"og:description\" content=\"Date formats in Sage CRM always seem to be an area of discussion. A Date is made of three elements namely Month, Date and Year and you can have various standard combinations depending on country to have a date format derived out of these three basic elements. While it is easy to interpret a DD\/MM\/YY\u2026 Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/\" \/>\n<meta property=\"og:site_name\" content=\"Sage CRM \u2013 Tips, Tricks and Components\" \/>\n<meta property=\"article:published_time\" content=\"2011-06-24T12:00:00+00:00\" \/>\n<meta name=\"author\" content=\"greysagecrm\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"greysagecrm\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/\",\"name\":\"Date Format with Suffix - Sage CRM \u2013 Tips, Tricks and Components\",\"isPartOf\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\"},\"datePublished\":\"2011-06-24T12:00:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Date Format with Suffix\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website\",\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/\",\"name\":\"Sage CRM \u2013 Tips, Tricks and Components\",\"description\":\"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f\",\"name\":\"greysagecrm\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g\",\"caption\":\"greysagecrm\"},\"url\":\"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Date Format with Suffix - Sage CRM \u2013 Tips, Tricks and Components","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/","og_locale":"en_US","og_type":"article","og_title":"Date Format with Suffix - Sage CRM \u2013 Tips, Tricks and Components","og_description":"Date formats in Sage CRM always seem to be an area of discussion. A Date is made of three elements namely Month, Date and Year and you can have various standard combinations depending on country to have a date format derived out of these three basic elements. While it is easy to interpret a DD\/MM\/YY\u2026 Read More »","og_url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/","og_site_name":"Sage CRM \u2013 Tips, Tricks and Components","article_published_time":"2011-06-24T12:00:00+00:00","author":"greysagecrm","twitter_card":"summary_large_image","twitter_misc":{"Written by":"greysagecrm","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/","name":"Date Format with Suffix - Sage CRM \u2013 Tips, Tricks and Components","isPartOf":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website"},"datePublished":"2011-06-24T12:00:00+00:00","author":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f"},"breadcrumb":{"@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/2011\/06\/24\/date-format-with-suffix\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/"},{"@type":"ListItem","position":2,"name":"Date Format with Suffix"}]},{"@type":"WebSite","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#website","url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/","name":"Sage CRM \u2013 Tips, Tricks and Components","description":"Explore the possibilities with Sage CRM insights through our comprehensive blogs. As a leading Sage partner, Greytrix helps businesses maximize their Sage CRM potential with its rich expertise and immense knowledge. Here, you will find blogs that feature expert advice, tips & tricks, best practices, and comprehensive guides on customizing and configuring Sage CRM for your business. Stay informed with our regular updates and expert insights!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/e7ff1c8f4763b47730d6bc5e74d59c1f","name":"greysagecrm","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c3b4325cb326e36467f945b9b9adf2bb85e907fe092d42eb36b39743b492e626?s=96&d=mm&r=g","caption":"greysagecrm"},"url":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/author\/greysagecrm\/"}]}},"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/334","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/comments?post=334"}],"version-history":[{"count":0,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/posts\/334\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/media?parent=334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/categories?post=334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagecrm\/wp-json\/wp\/v2\/tags?post=334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}