{"id":122,"date":"2009-04-15T07:54:52","date_gmt":"2009-04-15T13:54:52","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=122"},"modified":"2016-01-20T16:40:26","modified_gmt":"2016-01-20T22:40:26","slug":"ssrs-format-and-formatedatetime","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=122","title":{"rendered":"SSRS – Format() and FormateDateTime()"},"content":{"rendered":"

Found this reference … JUST IN TIME!<\/p>\n

Thanks Thavash <\/b>This is by far my most visited page.<\/p>\n

http:\/\/thavash.spaces.live.com\/blog\/cns!CF6232111374DFD2!155.entry<\/a><\/p>\n

April 10<\/div>\n

Working with Dates in Reporting Services<\/h4>\n
\n
As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today\u00a0, “How to I format dates when using the Date Picker and sending it through to a stored procedure” ? Let’s have a look ….<\/div>\n
<\/div>\n
1) The FormatDateTime <\/strong>command<\/div>\n
<\/div>\n
This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :<\/div>\n\n\n\n\n\n\n\n
Command<\/strong><\/td>\nResult<\/strong><\/td>\n<\/tr>\n
FormatDateTime(Parameters!Date.Value,1)<\/td>\nTuesday, April 10, 2007<\/td>\n<\/tr>\n
FormatDateTime(Parameters!Date.Value,2)<\/td>\n4\/10\/2007<\/td>\n<\/tr>\n
FormatDateTime(Parameters!Date.Value,3)<\/td>\n12:00:00 AM<\/td>\n<\/tr>\n
FormatDateTime(Parameters!Date.Value,4)<\/td>\n00:00<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

…but the better way to do it would be to use …<\/p>\n

2) The Format<\/strong> command and specify the exact format you require. For example…<\/p>\n\n\n\n\n\n\n\n
Command<\/strong><\/td>\nResult<\/strong><\/td>\n<\/tr>\n
Format(Parameters!Date.Value,”dd-MM-yyyy”)<\/td>\n10-04-2007<\/td>\n<\/tr>\n
Format(Parameters!Date.Value,”dd\/MM\/yyyy”)<\/td>\n10\/04\/2007<\/td>\n<\/tr>\n
Format(Parameters!Date.Value,”MMM-dd-yyyy”)<\/td>\nApr-10-2007<\/td>\n<\/tr>\n
Format(Parameters!Date.Value,”MMM-dd-yy”)<\/td>\nApr-10-07<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

So 3 M’s give you “Apr” ….anyway this is quite useful if you’re looking for Day\/Month\/Year , since the system will default to MM\/DD\/YYYY.<\/p>\n

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.<\/p>\n

EDITED 22\/08\/2007<\/strong> : If the Format Command doesn’t work , try converting the value to a date , eg.<\/p>\n\n\n\n
Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)<\/span> <\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

SSRS Date Formating reference.. Thanks Thavash!!! You are a Rock Star!<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[60,58,138,113,22,51,5],"tags":[280,303,260,339,338,67,265,315,249,35,36,295,52,298,291],"class_list":["post-122","post","type-post","status-publish","format-standard","hentry","category-dotnetnuke","category-os","category-programming","category-reportingbi","category-sql-server","category-ssrs","category-t-sql","tag-date-format","tag-dba","tag-developer","tag-format","tag-formatdatetime","tag-how-to","tag-learning","tag-query","tag-script","tag-select","tag-sql","tag-sql-server","tag-ssms","tag-ssrs","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/122","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=122"}],"version-history":[{"count":7,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/122\/revisions"}],"predecessor-version":[{"id":634,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/122\/revisions\/634"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}