Date display from MS SQL

[tmccartney66]

We just upgraded our account, going from CF6 to CF8 and from MS SQL 2000 (I think) to MS SQL 2005.

One of the features of our site is a court case database that allows users to search for a case name and have a list of results come up.  The date field in the list of results is pretty messy - it looks like this:

2009-12-09 00:00:00.0

It used to be much friendlier, although I can't remember exactly how it was outputting before.  I'd prefer MO-DAY-YEAR or Dec. 12, 2009.

The CF code that creates that results display hasn't changed, so I have to assume I need to tweak this in the database itself; the date is pulled from the "CaseDate" field with data type datetime.  I'm not incredibly well-versed in MS SQL, but I do have SQL Server Management Studio Express.

Any suggestions on where to start?

Thanks!

[r937]

best practice is to do custom formatting in the application, not in the SQL

use coldfusion's DateFormat() function

[MorningZ]

also check out SQL's "CONVERT" functionality

http://www.mssqltips.com/tip.asp?tip=1145

So if you have a date column where a value is:

2009-12-09 00:00:00.0

Saying

SELECT CONVERT(varchar, DateColumn, 107) FROM Table

will give you

Dec 09, 2009

[ak732]

If your website will be viewed by people out side the US (where CT's database server is located), then it's a good idea to let the DB server store and return the date and then use your client software (ColdFusion in this case) to format the date in the locale-specific fashion of the viewer instead of hard-coding it anywhere (particularly in a SQL query - which is, I think, what Rudy was saying).

[r937]

spot on, andy

presentation belongs in the application layer

[tmccartney66]

Thanks, guys.  I have no idea how to implement what you've suggested, but I'm already looking for a CF consultant, and I will ask him or her to put this on the list of things to be fixed.



Tracey

[Cliff]

Tracey,

In your first post you mentioned "The date field in the list of results is pretty messy - it looks like this: 2009-12-09 00:00:00.0"

Somewhere on that page is something that look like this:
<cfoutput>#DateField#</cfoutput>

This is showing the date field from the database.

To make it look "friendlier", try this:
<cfoutput>#DateFormat(DateField, 'mmm. dd, yyyy')#</cfoutput>

That should display how you prefer.

Keep in mind what ak732 said about locale-specific if that applies to you.