My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

Question: How do you create a DateTime in a specific format in SQL Server?

Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. This means that it is no longer a DateTime. It is a VARCHAR.

This might not be the answer you are looking for, but please ... don't stop reading!

. . .

There is definitely confusion about the concept of raw data versus the presentation of that data, and it comes to play often when trying to format dates in SQL. If you use CONVERT() in an attempt format your DateTime data, remember that it physically converts that data from DateTime to another data type! In fact, it's right there in the expression:

SELECT CONVERT(varchar(10),someDate)

That expression is clearly CONVERTing someDate to a varchar(10). What gets returned when you convert something to a VARCHAR, a date value or a string? The answer is a string of meaningless characters that no longer have any value as an actual date.

"You clearly are a hack, Jeff," you tell me, "since it is very easy for a SQL-Master such as myself to format dates without converting them to strings. For example, check this out this sweet 'mm/dd/yyyy' format:

I can write stuff like this all week! It's easy. You've got much to learn!"

Well, I have bad news for you. That still is implicitly converting everything to a string -- the rtrim() function is handling that part. This is even worse than doing it explicitly with a CONVERT() function, and the end result is not easy to read or work with or write, it is not efficient, and it is still notreturning a DateTime value.

. . .

Always remember: If a value is not a DateTime datatype, it is not a date. No matter what it looks like, or how neatly formatted you made that string, or how careful you were to use an ISO compliant format, it is not a Date. Period.

It is crucial to understand this, and to thus to understand the implications of trying to "format" data at the database layer. It cannot be done! All you can do is convert things to generic "string" datatypes. That's it.

. . .

No matter what they may look like, strings don't sort like dates. They don't compare like dates. You can't get the month from a string consistently, or calculate the amount of minutes between two strings, or add x days to a string. You can't ensure that different databases or stored procedures or functions or applications will always interpret your chosen date formatted string the same. Client applications -- who should be doing the formatting -- cannot apply date formatting to a string, they need an actual date stored in the correct data type. Thus, they would need to convert this string back to a DateTime type and only then can they format it for display purposes or use standard date calculations on the value. Does it really make sense to start with a date value, convert it to a string in SQL, and then have your client convert it back to a date value?

Simply return raw data from your database using the proper data types, and then simply use the tools designed to handle raw data in the correct types at your clients to format and present that data.

In crystal reports or other reporting tools, you can just drop your nice, clean, raw unformatted datetime value on your report, right-click it, and easily format it any way you want. You can use regional settings, specify mm/dd/yyyy format strings, and all kinds of options. It's simple and easy, but you must return datetime values back from SQL, not VARCHARS!

In Excel, again, you can simply right-click and choose any format you want, or create your own.

In .NET applications, you can usually format dates in data bound controls using the GUI interface, and you can also format things using the ToString() method of a true datetime value and specify all kinds of simple yet flexible formatting strings.

In ASP.NET web pages, just about all data bound web controls let you specify a FormatString property for your bound columns, giving you clear, simple control over exactly how your dates look.

In VB and VBA, there is a Format() function that again works with named formats or custom format strings.

In MS Access, the report and form designer lets you format any text box containing a datetime value any way you want, again with simple format names or format strings, and you have all of the VBA functions available to format dates in your code. You can even specify the specific date format for columns in a query in the query designer -- but, again, you must be working with data in the correct datetime data type.

Isn't it much easier to simply right-click on something and then enter a simple "mmm dd, yyyy" format string instead of building and parsing this manually using CONVERT and SUBSTRING parsing in T-SQL? Isn't it more flexible to do all formatting at your presentation layer so that you can just return data from your database and not worry about how it looks? Then 5 different clients can query the same stored procedure and each output those dates any way they want -- without changing any database code. Doesn't that make more sense?

Give it a shot -- format your dates at your presentation layer. You might be surprised how easy it is.

. . .

(Please, oh Google-gods, rank this article prominently, and let's save some poor, misguided souls who might be searching for "how to format a date in SQL Server". If just one person sees the light, then I've done my job! .... Yes, I am not above shamelessly begging the search engines for relevance .... it's called SEO, right?)

Feedback

Dates, their storage and display have got to be the most misunderstood aspect of a database.

People seem to:

1. Not understand that a date is held in only ONE format internally and then not in a way YOU'D want to see it 2. That the "date" they see in their report/query result is how the client/Query Analyser has determined it should be displayed according to formating configuration.

Rob -- I recommend asking your question in the sql team forums. And what is Slength? The number of seconds? Are you asking about to convert total seconds into hh:mm:ss ?? If so, then it is simple math, right?

if seconds = the total number of seconds, then:

hh = seconds / 3600mm = (seconds mod 3600) / 60ss = (seconds mod 60)

simple math is all you need, and now just format your 3 numbers any way you want at your presentation layer. This really has nothing to do with the dateTime datatype.

First off I find it interesting how "Anonymous" hasn't read the article :)

In comment #2 MikeyT writes:2. That the "date" they see in their report/query result is how the client/Query Analyser has determined it should be displayed according to formating configuration.

This suggests that there a setting in QA to change the way that dates are displayed (or infact accepted as input), is there?If so, can I change the setting to return dates in British format (dd/mm/yyyy) not American (mm/dd/yyyy)?

I will often writeWHERE DATE >= '01/06/2007'Expecting this to be interpreted as the 1st of June when QA sees it as the 6th of Jaanuary.A wee bit confused, so humour me! :)

QA returns dates as YYYY-MM-DD -- which is neither British or American, it is just a universal date format. I don't think you can easily change this in QA. It is definitely a short-coming in that application, it would be nice to have more control over how different data types are formatted and does lead to lots of confusion over presentation-versus-data when people feel forced to do it in T-SQL to see things the way they want in QA.

In SQL Server Management Studio it will use your computer's regional date time settings.

As for often writing literals as 'm/d/y' or 'd/m/y' my advice is: use neither. try to use the universal yyyy-mm-dd format and you'll be in good shape no matter what.

In general, it does really bother me that SQL lacks a quick function to generate a date without doing a conversion of some sort; I cannot for the life of me figure out why they would omit such as basic and crucial feature. If you can, I recommend that instead of using *any* formatting you use the functions here: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

and use the Date() function like this:

where date >= dbo.Date(year,month,day)

that way there is no converting, no formatting, no confusion or ambiguity at all. Lots of other good functions there that you might be interested in.

The Google-gods are working overtime here... I was actually searching to find the universal date format to get my application running properly accross a few servers with different setups, and only managed to find this in your last comment...Why not bump that format string up to your post and payer to get some more hits? ;)

lol at Anonymous, obviously didn't read the article...

Just to confirm what you try to say in the article is some people still have doubts, I had to use this formating method in SQL a week ago to get the hours part of the date when a transactions was processed:CONVERT(VARCHAR, RxDate, 114)

See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp to see what 114 is and how CONVERT work.

From this statement, it is clear that we convert to a VARCHAR so you are absolutely right by saying it is not a date anymore :)

Johannes -- thanks for the comment, but FYI -- you should not be using convert to get the time part of a DateTime; if you want good data with the correct data type and not just a formatted string, you should just return the time at the "zero" date of 1/1/1900. the functions here:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

will help you with that.

Once you have the time as a value (not a string), you can of course sort it and format it any way you want via your front end application or report. Returning the time as a varchar doesn't allow for that at all without converting it BACK to a DateTime type.

This has been a very interesting thread to read. I was trying to find out how to format the date portion of a datetime so as to display it on a report only. I did not need it for sorting or anything like that. This thread did provide me with what I needed.

That way I get a simple number I can format on the client, which happens to be Javascript. I really wish SQL had a simple way to SPECIFY dates in a similar format, seconds since 1970 UTC, by implicitly converting an integer.

my goodness, talk about missing the point. The point is that writing string manipulation code like that is not only error prone (as I demonstrated) but it is messy, convoluted, hard to read, and returns nothing but strings, not true date values.

Isn't it a little easier, instead of writing sloppy code like that, to simply format at the client as

I will put you a problem that your simple client date format manipulation cannot handdle.

Lets imagine that you have a table with a date field and with millions of records (lots per day). Lets imagine too, that the company handles the canadian week system. (This means that the grouping by cannot be handled from the client).

Canadian system: First week in the year is the one of the 4th of January.

Now lets imagine that you try to show a report grouping values by week and to show the information of the weeks in the following format yyyy/ww. SQLServer does not handdle the management of this week system at all. It just takes allways 31/12/2005 as week 53 of 2005 and 01/01/2006 as week 1 of 2006 no matter if they are in the same week. This makes the reports inconsistent as it may happen that the first and the last week of the year could have less than 7 days! (The correct output should be that both dates should be week 53 of 2006).

This is the reason why there cannot be any solution to this problem but parsing of dates into custon integer (200653) or custom string representations(2006/53).

Sometimes being a little more modest is not so bad as wondering a little more why the people would need to operate with string representations of dates.

>>This is the reason why there cannot be any solution to this problem but parsing of dates into custon integer (200653) or custom string representations(2006/53).

Great example, though probably not in the way you intended!

You should break the data elements down into separate columns, and return the year and the week number separately. Now, you have clean, clear data elements with string typing (both can be integers), you can sort or group or join on them separately, and your client can easily format the year and week no anyway that it wants but combining them or leaving them separate. If you truly have unique ways of calculating the week no per year, this should be stored in a table somewhere with start/end dates along with the year and weekNo columns -- all using correct data types, and always returning the correct data types to clients without combining things into pre-formatted strings.

As for being modest, not sure what that has to do with anything, but thanks for your comments anyway!

As always, when you request help - you'll find the solution yourself.In Microsoft SQL Server Reporting Services you have to click the blank part of the body,got to the properties pane on your right and under Language select your country.After this right click your field "datum value" go to properties, format click the the pointschose date - and voila youll get the date format for your country.

So far it was a successful day, i have just reformated 10 of my report after i couldn't fix this for about a monthand no ill get my well earned beer - but jeff no worries i still own you a beer :-)

Can i know how the SQL Server stores a Date, i mean in which format? Is it in "yyyy-MM-dd" format always, on every SQL Server by default. Because this is the format on my SQL Server. Is there any functionality to get/set the Date format on SQL server without converting it to 'varchar' i.e keeping its data type as Date only.

what the hell....i thought i am making some mistake. Microsoft cannot store the date as ONLY DATE, why the hell they are adding time stamp with date even if we don't want it........WHY SHOULD WE DO THIS THING AT PRESENATION LAYER? it is same like holding your left ear from behind your head with your right hand.....Is Someone from Microsoft reading this...

I was on the verge of tearing my hair out. I had searched, tried functions, calculated fields and numerous other fudges to get dates to be dd/mm/yyyy and to use the date picker in Access 2007.

If anyone at Microsoft is reading this - please add this to your (barely visible) documentation.

"If using SQL Server with MS Access dates will appear in the SQL Server native format (typically yyyy-mm-dd) regardless of format set in Access, UNLESS you use the datetime data type which can be converted to any format by access."

I now need to change a good number of fields in the database I'm building...

Thanks for the thorough explanation about dates. It helps you to realize the easiest way to manage dates in an application, i was struggling with it, but now i have to say; everything will be maintained with the help of some methods in the client app...