{"id":43,"date":"2008-04-18T14:28:22","date_gmt":"2008-04-18T19:28:22","guid":{"rendered":"http:\/\/www.cfchimp.com\/wordpress\/?p=43"},"modified":"2024-12-05T20:04:02","modified_gmt":"2024-12-06T01:04:02","slug":"cfgrid-dateformat","status":"publish","type":"post","link":"https:\/\/csimmons.dev\/blog\/2008\/04\/cfgrid-dateformat\/","title":{"rendered":"CFGRID DateFormat"},"content":{"rendered":"

For some reason when you have a datetime field in a cfgrid column it displays in a long format like this: Nov 17 2006 01:00:00<\/p>\n

Fig 1 (Below) A date displayed in cfgrid<\/strong><\/h6>\n

\"CFGRID<\/p>\n

If we work a little SQL magic (using “CONVERT”) in the cfquery though we can do the formatting we want, which is: 11\/17\/2006. Refer to the “styles” listed in Figure 3 to see what formatting is available.<\/p>\n

Coldfusion Query:<\/strong>
\n
\nSELECT note_id,note_text,
\nCONVERT(varchar,note_date,101) as note_date
\nFROM tbl_notes
\nORDER BY note_date
\n<\/cfquery><\/code><\/p>\n

Fig 2 (Below) A date displayed in cfgrid after using CONVERT in cfquery<\/strong><\/strong><\/h6>\n

\"CFGRID<\/p>\n

Fig 3 (Below) A <\/strong><\/strong>helpful list of “styles” to use with the SQL CONVERT function<\/h6>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Style ID<\/font><\/strong><\/td>\nStyle Type<\/font><\/strong><\/td>\n<\/tr>\n
0 or 100<\/td>\nmon dd yyyy hh:miAM (or PM)<\/td>\n<\/tr>\n
101<\/td>\nmm\/dd\/yy<\/td>\n<\/tr>\n
102<\/td>\nyy.mm.dd<\/td>\n<\/tr>\n
103<\/td>\ndd\/mm\/yy<\/td>\n<\/tr>\n
104<\/td>\ndd.mm.yy<\/td>\n<\/tr>\n
105<\/td>\ndd-mm-yy<\/td>\n<\/tr>\n
106<\/td>\ndd mon yy<\/td>\n<\/tr>\n
107<\/td>\nMon dd, yy<\/td>\n<\/tr>\n
108<\/td>\nhh:mm:ss<\/td>\n<\/tr>\n
9 or 109<\/td>\nmon dd yyyy hh:mi:ss:mmmAM (or PM)<\/td>\n<\/tr>\n
110<\/td>\nmm-dd-yy<\/td>\n<\/tr>\n
111<\/td>\nyy\/mm\/dd<\/td>\n<\/tr>\n
112<\/td>\nyymmdd<\/td>\n<\/tr>\n
13 or 113<\/td>\ndd mon yyyy hh:mm:ss:mmm(24h)<\/td>\n<\/tr>\n
114<\/td>\nhh:mi:ss:mmm(24h)<\/td>\n<\/tr>\n
20 or 120<\/td>\nyyyy-mm-dd hh:mi:ss(24h)<\/td>\n<\/tr>\n
21 or 121<\/td>\nyyyy-mm-dd hh:mi:ss.mmm(24h)<\/td>\n<\/tr>\n
126<\/td>\nyyyy-mm-dd Thh:mm:ss.mmm(no spaces)<\/td>\n<\/tr>\n
130<\/td>\ndd mon yyyy hh:mi:ss:mmmAM<\/td>\n<\/tr>\n
131<\/td>\ndd\/mm\/yy hh:mi:ss:mmmAM<\/td>\n<\/tr>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

For some reason when you have a datetime field in a cfgrid column it displays in a long format like this: Nov 17 2006 01:00:00 Fig 1 (Below) A date displayed in cfgrid If we work a little SQL magic (using “CONVERT”) in the cfquery though we can do the formatting we want, which is: … 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,"_cloudinary_featured_overwrite":false,"_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":""},"categories":[68],"tags":[48,16],"class_list":["post-43","post","type-post","status-publish","format-standard","hentry","category-developer","tag-coldfusion","tag-sql"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pbVg43-H","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/posts\/43","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/comments?post=43"}],"version-history":[{"count":0,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/posts\/43\/revisions"}],"wp:attachment":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/media?parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/categories?post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/tags?post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}