September 29, 2007 at 2:34 am
I use VB6 ad have a big headache juggling date formats in my apps. One of the issues is the combination of dates and times. We need to truncate the time part in all our date processing. Then we need to bear the user's date format settings in mind. So we keep formatting our dates throughout the process, when reading from the DB, processing, and storing back to DB. This is a major headache for me, though I am puzzled how other developers get round it.
Someone on another forum recommended extracting preformatted date values from SQL as such:
Select (DatePart(dd, ColDate) +'/'+ DatePart(mm, ColDate)+'/'+ DatePart(yyyy, ColDate)) As NewDateFormat From SomeTable.
QA won't accept this, so I tweaked it to reach here:
Select (CONVERT(VARCHAR, DatePart(dd, ColDate)) +'/'+ CONVERT(VARCHAR, DatePart(mm, ColDate)) +'/'+
CONVERT(VARCHAR, DatePart(yyyy, ColDate)))
As NewDateFormat From SomeTable.
I searched BOL for more examples of CAST and CONVERT functions and tried to apply Date Styles, etc all in vain.
Where can I get a good article / tutorial on these two functions?
September 29, 2007 at 3:26 am
If I may suggest, do your formatting on the front end, not in SQL. VB has far more options for date formats than SQL does and it quite a bit more flexible.
That said, if you want your date in the format dd/mm/yyyy, you're looking at convert with a style of 103
SELECT CONVERT(VARCHAR(10), ColDate, 103) AS FormattedDate FROM myTable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2007 at 5:08 am
Check out books online in the convert section. You'll get all 10 000 possible presentation you can have in SQL Server. That said, do the formatiing in the front APP if possible is a good advice, but on small queries, on a relatively unbusy server, you won't see any difference on the server load if you do the convert on sql instead of APP.
September 29, 2007 at 5:25 am
September 29, 2007 at 6:10 am
There are good arguments from both sides. As a DBA, my job is to ensure the correct results with the BEST server performance. So the less work I do on the server the better. However there are arguments that if multiple apps are using the same data and they all need the exact same format across the board, then letting the server do it makes a lot of sens.
This is a best practice you can easily bend on sql server, but it's nice to have it in mind.
September 29, 2007 at 8:10 am
Logic here is simple:
1) formatting is user-dependent. One day they gonna request you to let different users have different formatting. What you gonna do?
2) Formatting requires string-processing. You have a choice either to put the whole load on server's CPU or distribute it over client's machines (which are typically most of the time not busy, just waiting for response either from server or from console).
3) VB6 is not only application which can connect to SQL Server. One day they gonna ask you to use another platorm (probably in parralel with existing VB application). What you gonna do with all your VB-oriented SPs ?
_____________
Code for TallyGenerator
September 29, 2007 at 9:18 am
Wow, that's the clearest explaination I ever saw for that question. Thanks Sergiy.
September 29, 2007 at 9:54 pm
I got the following from a fellow by the name of "Jeff Smith"... it's pretty much spot on and is a long winded version of what Serqiy posted, which is also, spot on.
WHY You Probably Shouldn't Format Data in SQL Server
Unless it's purely for reporting purposes, it's usually a bad idea to send preformatted data to an application from SQL Server. For example, if you add a "$" or commas to a numeric data type, you must first convert it to VARCHAR. If you want to actually use the number for a calculation in the app, the app must either implicitly convert it back to a number (which may no longer be possible because of the extra characters) or the app must explicitly (through code you must write) convert it back. Further, things like thousands separators are different from country to country (commas in U.S.A., periods in many other places) and apps set up to automatically change the way those thousands separators are displayed depending on which country the machine is in, will no longer work correctly. Telephone number formats are also another thing to avoid in SQL for similar reasons.
Same goes with dates… if you format the date on the SQL Server side, you may render it useless for any date calculations you may need to do in the app unless you write code to explicitly convert it back to a date/time. And, if you work for an international company, the app is probably (should be) already setup to detect which country the client computer is in and to display date/time data types correctly for that country. If you pre-format the date, you will be disabling that functionality.
It's just like storing dates and times in two separate VARCHAR or CHAR columns in the database… in order to do correct date/time calculations, you must first concatenate the two columns into a format that can be converted back to a date/time data type, do the actual conversion, and then do your calculation.
Whether sending data to an app or storing it in a database, it will usually cause some severe conversion pain and, perhaps, a loss of functionality to send or store preformatted data even if it's simple right hand justification.
The exception to the rule may be (it's not always) when the data is used purely for reporting or, in the case of creating flat files for export, may have no app associated with it. Even then, you should always ask the question, "Will this formatted data be used in a calculation or conversion in the app, itself?" If you don't know for sure that is isn't and never will be, don't format the data on the SQL side.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2007 at 12:49 am
October 1, 2007 at 6:22 am
With regard to headaches on how to write dates back to the DB, this is a must read:
'How do I delimit/format dates for database entry?'
/Kenneth
November 1, 2007 at 4:13 am
goodguy (9/29/2007)
I use VB6 ad have a big headache juggling date formats in my apps.
One thing I used frequently in VB6 to remove the time was the DateSerial function into which I pass calls to Year, Month and Day of the ADO field containing the date. Thus nothing needs to be done on the SQL side. And you can enclose this in a function such as
Function TruncDate(adoDate As ADODB.Field) As Date
TruncDate = DateSerial(Year(adoDate), Month(adoDate), Day(adoDate))
End Function
November 1, 2007 at 6:24 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply