{"id":592,"date":"2013-05-24T15:52:07","date_gmt":"2013-05-24T20:52:07","guid":{"rendered":"https:\/\/danvanfleet.com\/?p=592"},"modified":"2013-06-03T08:10:26","modified_gmt":"2013-06-03T13:10:26","slug":"sql-convert-yyyymmdd-integer-or-string-to-a-date-that-works-in-softpro","status":"publish","type":"post","link":"https:\/\/danvanfleet.com\/index.php\/sql-convert-yyyymmdd-integer-or-string-to-a-date-that-works-in-softpro\/","title":{"rendered":"SQL convert "yyyyMMdd" integer or string to a Date that works in SoftPro"},"content":{"rendered":"

The last few weeks I’ve been working with Web based Reporting for SoftPro<\/a> ProForm.  It’s been fun creating management review pages with dials and lights.<\/p>\n

\"image\"<\/a> <\/p>\n

I’ve enjoyed combining multiple report outputs on one concise easy to ready web page.  Along the way, it’s been necessary to display SoftPro<\/a> Database formatted date fields in a grid.  They come through in 20130524 or “yyyyMMdd” format.  It was easy enough to format that in a grid as “yyyy-MM-dd” format which worked until I started exporting the grids to Excel, when the formatting failed.  So I began a search to fix it, at the SQL level.<\/p>\n

<\/p>\n

There are a ton of examples of how to do this, they don’t work for SoftPro<\/a> Standard or Enterprise Databases, as when nothing is in the field, a “0” zero is stored, that caused a basic conversion to fail. <\/p>\n

Out came the Case statement with a solution!<\/p>\n

\n
\n
SELECT<\/span> CASE<\/span> WHEN<\/span> Search<\/span>.OrdDate = 0 THEN<\/span> NULL<\/span> <\/pre>\n
<\/p>\n
ELSE<\/span> CAST<\/span>(CAST<\/span>(Search<\/span>.OrdDate AS<\/span> CHAR<\/span>(8)) AS<\/span> Date<\/span>) END<\/span> as<\/span> OrderDate From<\/span> Search<\/pre>\n
<\/div>\n<\/div>\n
So we check the field to see if it’s a zero, if so, send back a null to a column called OrderDate, if it’s not a zero, cast or change the field from being an integer to a Date.  This routine does a similar thing to the Crystal Reports Formula<\/p>\n
\n
\n
Numbervar Year1:=Truncate<\/span>( {DatesTimes.OrdDate} \/ 10000 );<\/pre>\n
<\/p>\n
Numbervar Month1:=Truncate<\/span>(({DatesTimes.OrdDate}-(Year1*10000))\/100);<\/pre>\n
<\/p>\n
Numbervar Day1:=Truncate<\/span>({DatesTimes.OrdDate}-Truncate<\/span>({DatesTimes.OrdDate}\/100)*100);<\/pre>\n
<\/p>\n
Date<\/span>(Year1,Month1,Day1);<\/pre>\n
<\/div>\n<\/div>\n<\/p>\n
I wonder if the same methodology would work in a Crystal Selection Formula, but since I started doing these Web based reports, I don’t expect to live in Crystal for most reporting needs.  This Live Report concept for SoftPro<\/a> products is just awesome, we can export the grid to Excel with perfect formatting, add headers and footers if necessary, and it’s all much faster than generating a Crystal Report.  Most reports have been running in a few seconds, so in under a minute you can look at 8 or 10 different reports.  Or just load up the Right Now page to see the aggregate numbers of selected reports in a dashboard style.<\/p>\n
If you need to calculate the difference between dates, pull out the SQL command DATEDIFF(day, Date1CaseStatement, Date2CaseStatement) as DateDifferenceColumn.  <\/p>\n","protected":false},"excerpt":{"rendered":"
The last few weeks I’ve been working with Web based Reporting for SoftPro ProForm.  It’s been fun creating management review pages with dials and lights. I’ve enjoyed combining multiple report outputs on one concise easy to ready web page.  Along the way, it’s been necessary to display SoftPro Database formatted date fields in a grid.  […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[23],"tags":[27,13,17],"class_list":["post-592","post","type-post","status-publish","format-standard","hentry","category-softpro-2","tag-reports","tag-sql","tag-tips","entry"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4wyVb-9y","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/592","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/comments?post=592"}],"version-history":[{"count":1,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/592\/revisions"}],"predecessor-version":[{"id":607,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/posts\/592\/revisions\/607"}],"wp:attachment":[{"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/media?parent=592"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/categories?post=592"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/danvanfleet.com\/index.php\/wp-json\/wp\/v2\/tags?post=592"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}