Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

How to format a Date or DateTime in SQL Server

by Jeff Smith in  , , , ,

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:  

select right('0' + rtrim(month(@d)),2) + '/' + right('0' + rtrim(day(@d)),2) + '/' + rtrim(year(@d)).

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 not returning 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?

It's easier.  It's shorter. It's more efficient.  it's more flexible. It's more standard.  It's easier to maintain. It's less code to write!

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?)

see also:

Legacy Comments
Mladen
2007-04-14		 re: How to format a Date or DateTime in SQL Server
i like your prayer jeff :)))
MikeyT
2007-04-18		 re: How to format a Date or DateTime in SQL Server
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.

Nice work in the struggle against date abuse Jeff.
g8dnitez
2007-05-06		 re: How to format a Date or DateTime in SQL Server
my query :
Select convert(varchar(100), (
Select [@STOCKRESERVE].U_TransDate from [@STOCKRESERVE], OCRD, OITM where [@STOCKRESERVE].U_CustNo = OCRD.CardCode AND [@STOCKRESERVE].U_ItemCode = OITM.ItemCode AND OCRD.CardType = 'C' AND [@STOCKRESERVE].U_Status ='N'))


return error :
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


can help me to solve this problem? thx
Jeff
2007-05-07		 re: How to format a Date or DateTime in SQL Server
g8nditez -- I have no clue what you are trying to accomplish there .... my advice to you is to read the article.
Rob
2007-05-08		 re: How to format a Date or DateTime in SQL Server
I haqve a Database of time entered.

I have :

Table Name sessionTime

ID
startTime = Date/Time (EX: 5/3/2007 10:50:00 AM)
EndTime = Date/Time (EX: 5/3/2007 10:50:00 AM)
Slength = 6135

I have many entries. When summing up

select Sum(slength)


from sessiontime where taskid=644

returns "61637153"

How do I then convert (61637153) To hh:mm:ss??????
Jeff
2007-05-08		 re: How to format a Date or DateTime in SQL Server
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 / 3600
mm = (seconds mod 3600) / 60
ss = (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.
Jose Figueiredo
2007-05-25		 re: How to format a Date or DateTime in SQL Server
g8dnitez:

The subquery returns multiple values and can't be passed as an argument. You should use this query (not tested):

Select convert(varchar(100), [@STOCKRESERVE].U_TransDate)
from [@STOCKRESERVE], OCRD, OITM
where [@STOCKRESERVE].U_CustNo = OCRD.CardCode AND [@STOCKRESERVE].U_ItemCode = OITM.ItemCode AND OCRD.CardType = 'C' AND [@STOCKRESERVE].U_Status ='N'
Anonymous
2007-06-06		 re: How to format a Date or DateTime in SQL Server
Seems like more of a rant than of any helpful advice.

The problem is that what happens when SQL is the presentation layer? i.e. emails being sent from SQL where you want to display the date as Thursday June 5, 2007 5:52pm

It doesn't need to BE a date at that point, but needs to be formatted for display in the email or other text/varchar format.

So your article is actually incorrect, as formatting a date CAN be done, it just is messy. I was hoping by reading this for a cleaner answer, not a rant...
georgev
2007-06-14		 re: How to format a Date or DateTime in SQL Server
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 write
WHERE 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! :)
Jeff
2007-06-14		 re: How to format a Date or DateTime in SQL Server
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.

Thanks for the feedback, georgev -- I hope this helps
georgev
2007-06-15		 re: How to format a Date or DateTime in SQL Server
where date >= dbo.Date(year,month,day)

That's perfect - so simple! Thanks for the advice Jeff :)
~George
Johannes
2007-07-10		 re: How to format a Date or DateTime in SQL Server
Hi Jeff,

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 :)
Jeff
2007-07-11		 re: How to format a Date or DateTime in SQL Server
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.
Kerry
2007-07-11		 re: How to format a Date or DateTime in SQL Server
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.

Thanks for the information everyone!
Uwa
2007-07-28		 re: How to format a Date or DateTime in SQL Server
See http://www.mssqltips.com/tip.asp?tip=1145 on how to return a formated date in SQL Server (or refer to SQL Books online)
Dave
2007-07-31		 re: How to format a Date or DateTime in SQL Server
I tend to do a lot of this:

DATEDIFF(second, '1 Jan 1970', tbl.LastChangeDate)

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.
samir
2007-08-13		 re: How to format a Date or DateTime in SQL Server

How to format a Datetime value to Date in SQL Server.
eg. i have value 1/1/2007 01:02:46 in database but i want only date from that field.
konrad
2007-08-17		 re: How to format a Date or DateTime in SQL Server
Correction to the above answer when reformatting a date to a YYYY-MM-DD string. I think it should be:


declare @d as datetime
set @d = '2007-10-01'

select rtrim(year(@d)) + '-' + right('0' + rtrim(month(@d)),2) + '-' + right('0' + rtrim(day(@d)),2)

ie. rights not lefts
capt
2007-08-28		 re: How to format a Date or DateTime in SQL Server
how can i add the date today by 3

$datetoday=date('Y-m-d');

if datetoday 2007-08-29

added by 4

i will get 2007-09-1

and not 2007-08-33
georgev
2007-09-11		 re: How to format a Date or DateTime in SQL Server
Hey capt - I suggest you take a look at the DateAdd() function available in SQL Server.
Sedwick
2007-09-24		 re: How to format a Date or DateTime in SQL Server
I second Konrad's response. The original in the top post formatted August 31st as 08/03.
Jeff
2007-09-24		 re: How to format a Date or DateTime in SQL Server
Sedwick / Konrad --

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

mm/dd/yyyy

??????
Jon
2007-10-04		 re: How to format a Date or DateTime in SQL Server
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.
Jeff
2007-10-05		 re: How to format a Date or DateTime in SQL Server
>>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!
tongln
2008-03-23		 re: How to format a Date or DateTime in SQL Server
IsActive = 1 and date > StartDate and @date < EndDate


Is there correct >???
rhapsody
2008-07-24		 re: How to format a Date or DateTime in SQL Server
Hi Jeff,

Thanks. Your query had helped me alot. :)
sooraj
2008-07-25		 re: How to format a Date or DateTime in SQL Server
hi jeff
i had a confusion about this. now it is cleared. nice work. thanks
niraj kumar
2008-07-25		 re: How to format a Date or DateTime in SQL Server
nice work.
lina
2008-08-24		 re: How to format a Date or DateTime in SQL Server
What is the SQL statement to display the hiredates in the following formats
a) jan 31 90
b)01/03/90

thanks
Lyn
Mike
2008-10-22		 re: How to format a Date or DateTime in SQL Server
Hi Jeff,
really interesting conversation hear above, as i am now searching for hours and hours thru blogs, error pages and so on
i think you're the only one who get what i was thinking about.

I also have a problem to display a date in European style, so far maybe you can help me! i do not want to change the
format like datetime but how it is displayed.

The date as i get it is "20081022 6:00:00" but for my ceo it has to look like " 22.10.2008 18:00"
I am using Server Reporting Services 2005, Locale EN,

I probably have tried about 30 diffrent ways to get the resulst i wannt but no success :-(

So if you have any hint idea for me i own you a beer !
Cheer mike Slovenija
Mike
2008-10-22		 re: How to format a Date or DateTime in SQL Server
BIG SMILE :-)

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 points
chose 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 month
and no ill get my well earned beer - but jeff no worries i still own you a beer :-)

Se ya guy - nice evening!
Joe
2008-11-10		 Wrong article title
You incorrectly titled your post. It is supposed to be "BITCHING ABOUT FORMATTING SQL DATETIMES". Congradulations on getting paid. I'll never come back.
sanjay jagtap
2009-01-30		 re: How to format a Date or DateTime in SQL Server
any one help me

i using sql Express but i exist two problem

1) i want to insert only date in database but it insert 12-02-2009 00:00:00 formate in asp.net

2)if i insert date 12-02-2010 it show error dateTime out of rang please tell me how solve
sanjay
2009-01-30		 re: How to format a Date or DateTime in SQL Server
any one help me

i using sql Express but i exist two problem

1) i want to insert only date in database but it insert 12-02-2009 00:00:00 formate in asp.net

2)if i insert date 12-02-2010 it insert 12-02-1900 please tell me how solve
toni
2009-02-03		 re: How to format a Date or DateTime in SQL Server
how do I query using a Where and Like to pull only the month of Decemeber ? is it '%12/__/__ thanks!
Someone
2009-02-27		 re: How to format a Date or DateTime in SQL Server
you smarmy bastard!
Adjo
2009-03-04		 re: How to format a Date or DateTime in SQL Server
Refreshing view on how to keep life simple. Gives hope to the ones that are not yet sqlserver gurus but have gotten into the sqlserver date horrorshow.
Tuna
2009-03-13		 re: How to format a Date or DateTime in SQL Server
So, in a query, inline, is there no way to format the YYYY/MM/DD HH:MM:SS to MM/DD/YYYY ?
Shweta Bhandari
2009-03-19		 re: How to format a Date or DateTime in SQL Server
Hi,

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.

Please reply.
Yasser
2009-07-18		 re: How to format a Date or DateTime in SQL Server
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...
Ajay Kumar
2009-08-17		 re: How to format a Date or DateTime in SQL Server
My code is
first page test1.html


<html>
<body>
<form method = "post" action = "test1.jsp">
<input type = "text" name = "serialno">
<input type = "text" name = "samay">
<input type = "submit" name = "submit" value = "save">
</form>
</body>
</html>


my second page is test1.jsp

<html>
<body>
<%@ page import="java.sql.*" %>
<%
String serialno = request.getParameter("serialno");
String birth = request.getParameter("samay");
out.println(serialno);
out.println(birth);

try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:status");
Statement st = con.createStatement();
String query = "INSERT INTO test (serialno, dateofbirth) VALUES ('"+ serialno +"', #" +birth+ "#)";
int count = st.executeUpdate(query);
out.println("date entered");
st.close();
con.close();
}
catch(Exception e)
{
out.println("Error " + e);
}


%>
</body>
</html>



i am getting the error:

5/5/9 Sun Jul 12 00:00:00 IST 2009 0 0 Sun Jul 12 00:00:00 IST 2009 2009-07-12 Errorjava.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]The name "#5" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


i am using ms sql server 2005 express
status is ms sql server database defined in ODBC

please locate the bug

Ajay Kumar
Brad
2009-09-09		 re: How to format a Date or DateTime in SQL Server
The easiest way to format the date to MM/DD/YYYY is this:

Convert(varchar,datefield,101)
Dominiek
2009-10-28		 re: How to format a Date or DateTime in SQL Server
For anyone reading this and just looking for a solution instead of a rant, look here:http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html
Greg Robson
2009-11-19		 re: How to format a Date or DateTime in SQL Server
Just wanted to say thank you!

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...
Chandrakanta Kar
2010-01-04		 re: How to format a Date or DateTime in SQL Server
Please try this:
It might help you.

SELECT convert(varchar, getdate(), 22);

above will generate mm/dd/yy hh:mm:ss AM (or PM)
Damien
2010-01-07		 re: How to format a Date or DateTime in SQL Server
Thanks for this. I was initially looking for tips on writing code to perform this exact same function but now wont be wasting any more of my time. Good explanation.
Egli Becerra
2010-01-19		 re: How to format a Date or DateTime in SQL Server
Oh my god what a shit post of course u can what do u mean by u cant pls... do your research properly...
Lakshmanan from INDIA
2010-02-04		 re: How to format a Date or DateTime in SQL Server
Select getdate() -- 2010-02-05 10:03:44.527

-- To get all date format
select CONVERT(VARCHAR(12),getdate(),100) +' '+ 'Date -100- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(10),getdate(),101) +' '+ 'Date -101- MM/DDYYYY'
Union
select CONVERT(VARCHAR(10),getdate(),102) +' '+ 'Date -102- YYYY.MM.DD'
Union
select CONVERT(VARCHAR(10),getdate(),103) +' '+ 'Date -103- DD/MM/YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),104) +' '+ 'Date -104- DD.MM.YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),105) +' '+ 'Date -105- DD-MM-YYYY'
Union
select CONVERT(VARCHAR(11),getdate(),106) +' '+ 'Date -106- DD MMM YYYY' --ex: 03 Jan 2007
Union
select CONVERT(VARCHAR(12),getdate(),107) +' '+ 'Date -107- MMM DD,YYYY' --ex: Jan 03, 2007
union
select CONVERT(VARCHAR(12),getdate(),109) +' '+ 'Date -108- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(12),getdate(),110) +' '+ 'Date -110- MM-DD-YYYY' --02-05-2010
union
select CONVERT(VARCHAR(10),getdate(),111) +' '+ 'Date -111- YYYY/MM/DD'
union
select CONVERT(VARCHAR(12),getdate(),112) +' '+ 'Date -112- YYYYMMDD' -- 20100205
union
select CONVERT(VARCHAR(12),getdate(),113) +' '+ 'Date -113- DD MMM YYYY' -- 05 Feb 2010


SELECT convert(varchar, getdate(), 20) -- 2010-02-05 10:25:14
SELECT convert(varchar, getdate(), 23) -- 2010-02-05
SELECT convert(varchar, getdate(), 24) -- 10:24:20
SELECT convert(varchar, getdate(), 25) -- 2010-02-05 10:24:34.913
SELECT convert(varchar, getdate(), 21) -- 2010-02-05 10:25:02.990


---==================================
-- To get the time
select CONVERT(VARCHAR(12),getdate(),108) +' '+ 'Date -108- HH:MM:SS' -- 10:05:53

select CONVERT(VARCHAR(12),getdate(),114) +' '+ 'Date -114- HH:MM:SS:MS' -- 10:09:46:223
SELECT convert(varchar, getdate(), 22) -- 02/05/10 10:23:11 AM
----=============================================
SELECT getdate()+1
SELECT month(getdate())+1
SELECT year(getdate())+1
Mark
2010-03-03		 re: How to format a Date or DateTime in SQL Server
The CONVERT is great with the style, but how do I know which style to use? Meaning, can I interegate the OS and find out which style number to use for the box it is loaded on? Maybe a C# script?

Thanks
Al
2010-04-19		 re: How to format a Date or DateTime in SQL Server
How to get data of all projects that have the same starting date?

Pleace Help!
Thank you
DIEGO
2010-04-22		 re: How to format a Date or DateTime in SQL Server
hey Jef good job up there but,a picture is worth 1000 words so, a couple of examples would have speared you a lot writing
and some of us like me would have understood it better. thx anyway
Meenatchi
2010-04-23		 date formatStyles
Do we have any system table/view/proc/fucntion which will list all the date format styles in sql server?
Robert
2010-05-05		 re: How to format a Date or DateTime in SQL Server
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...

Again, thanks!
dave76
2010-06-09		 re: How to format a Date or DateTime in SQL Server
Question: How do you get Jeff to communicate in a respectful, non-condescending manner?

Answer: You don't. You can't.

This blog is worthless. Period. End of Story. That's it!

I won't be returning

dave
Mat
2010-06-29		 re: How to format a Date or DateTime in SQL Server
I have to convert the raw data returned by SQL server into HTML using XSL. How am I supposed to format dates?
Jeff
2010-06-29		 re: How to format a Date or DateTime in SQL Server
Mat --

http://www.w3.org/TR/xslt20/#format-date