Some custom formats can be obtained combining substrings of different CONVERT styles.
SQL SERVER CONVERT DATETIME TO STRING CODE
If the desired format is not in the list, you’re out of luck: implementing a custom format can turn into a nightmare and make your code bulky and unreadable. Sadly enough, the styles table contains the most common formats in western countries, but doesn’t offer a ready-made option for all the locales around the world. SELECT CONVERT(varchar(30), GETDATE(), 113) AS EuropeanDate If you are lucky enough to find the desired format in the Date and Time styles table showing in BOL, you can use the “style” parameter to obtain the formatted string. The built-in method (so far)Īs an evidence that converting from string to date and back is not an heretic crime, T-SQL comes with the built-in CONVERT function, which can handle the most common date and time formats. The same considerations can be applied to parsing dates from a string: the best place to do this is your application, so you’d better save yourself the headaches that doing it in SQLServer brings. A typical example is database mail, where the sp_send_dbmail stored procedure can accept a query parameter to extract some data and attach it to the message: in this case, there is no way to control how a date column gets formatted and the only option is choosing the appropriate format in advance.
However, in some particular cases, a formatting function in T-SQL can come handy, especially when the database and application layers are collapsed. This is exactly why you don’t want to store dates in a varchar column you don’t want to feed applications with dates stored as strings because that makes the information impossible to compare, sort, and treat properly with date functions.Īn application that receives a date in its string representation has to parse it to convert it back to a date: it is like using MSPaint as a word processor and extracting the text with an OCR software! Humans can read strings, but applications can handle temporal data only when it is stored in the internal representation of the programming language. In turn, the applications that retrieve temporal data from SQL Server treat dates in their own internal format and must use a formatting function to convert the internal representation in a human readable format. SQL Server is not a presentation software, it is a relational database engine, which stores dates in their internal binary representation and returns values to external applications using the client software’s binary representation. The best methodĭon’t format dates: you don’t need to and you don’t have to. SQL Server offers different possibilities to accomplish the task: in this article I will try to discuss and compare some of them and hopefully, help you decide which one best suits your needs. While I heartedly agree with those answers, I must admit that I had to deal with some cases where the conversion had to be performed in the database and I will probably need that again someday. The canned answers people usually get are along the lines of “Don’t format dates in T-SQL” and “Don’t store dates in character columns”, with slight variations. Or, even more often: “How do I parse a date from this character column?”. One of the questions I see asked over and over on the forums is “How do I format this datetime column with this particular format string?”.
0 kommentar(er)
