2004.11.10 06:58 AM

SQL Server Date-Only from DATETIME

As one of the messages in this thread suggests, the question of how best to return only the date portion of a DATETIME value in SQL Server, specifically in DATETIME format, has got to be one of the top 5 SQL Server questions of all time.  There are many solutions, including many which rely on some form of string parsing, which I dislike.  I've always used this:


I searched around to see whether others were using this technique and found a message from "CELKO" (the Joe Celko?), which illustrates a similar technique, except that he uses CEILING instead of FLOOR, which means you get the next day if there's a time component.

Alternatively, you can let SQL Server round it at noon by first converting to an INT:


On a slightly related note, KB article 327080 describes how ADO randomly rounds SQL Server milliseconds into seconds.


Hey thanks for this post. I had always ended up using the datepart parsing - long winded and honestly painful way to do this. Never thought to look for another solution since that is what someone else had shown me.

Ted | 2004.12.07 07:28 PM

Is there a reason not to use:


Jaap | 2005.02.18 07:52 AM

Hi Jaap,

Not as long as you don't mind having the date rounded up to the next day at noon.

ewbi.develops | 2005.02.18 08:26 AM

How about:

CAST(CONVERT(varchar, GetDate(), 101) AS DateTime)

| 2005.05.09 02:26 PM

That'll work. I've seen it before (may even have used it in the past?), but I'm not a fan. It relies on CONVERT, whose style codes (101 in this case) I find obscure and hard to remember. And, it involves two string conversions: CONVERT makes one, and CAST parses it. If I get a minute maybe I'll run some tests to see the actual impact of these string conversions over millions of rows. Maybe I'm just being delusional. Thanks!

ewbi.develops | 2005.05.09 06:19 PM


| 2005.07.11 03:11 PM

Very nice.

Get the difference between the 0 date and the target date in days and add them to the 0 date. Bingo, no more time.


ewbi.develops | 2005.07.11 03:20 PM

DATEADD(dd, DATEDIFF(dd,0,@DateTime), 0)

works quite nicely

ThePerfessor | 2005.07.11 04:17 PM

Very cool , thank

kawpod | 2005.08.24 09:27 PM

Very Nice

Deonad | 2005.10.10 02:07 AM

To answer the question above I ran both on 1,124,286 records.

The DATEADD method averaged a time of 6,556 while the CONVERT method averaged 6,414. That tells me the difference isn't much to consider.

SpectreGadget | 2006.08.17 11:24 AM

SpectreGadget, thanks for the timings. I will concede that I am in fact delusional. But, I retain my dislike for CONVERT's obscure style codes. ;)

Thanks again!

ewbi.develops | 2006.08.17 11:58 AM

Thanks - but how do you drop the 0:00 time? Left causes the date format to change first.

John | 2006.10.04 07:50 AM

Hi John,

Just to be clear, the examples above all deal with dropping (or rounding) the time portion of a DATETIME value. The end result is still a DATETIME value, but with the time portion equal to 0.

Formatting a DATETIME value for presentation, which is what I think you're asking, is a different deal. In short, to affect the presentation of a DATETIME value, you will have to CONVERT/CAST it to a different data type, usually a VARCHAR or other string format. The CONVERT function makes this easy, as it provides a third optional parameter allowing you to specify the format you'd like the DATETIME to assume following conversion.

So, for instance, the following will convert a DATETIME to a VARCHAR formatted as mm/dd/yyyy (code 101):


Good luck.

ewbi.develops | 2006.10.04 08:31 AM


This will give you the right way of presenting data. But be careful while sorting! Since this returns a varchar you might have have unexpected results when you start sorting data based on date

kalyur | 2006.10.10 03:27 PM

I also need some help about converting.
I need a computed column decimal wich must be the quantity of Hours between A and B
A and B are char(5) -> for input something like 11:30

A and B must be char(5)

I try this, in the formula property, but gives me an error


Antonio Afonso | 2006.10.19 03:23 AM


That's interesting. I get the error, too. In this case the error simply means that SQL Server can't verify the formula, but it does not mean that the formula won't work. If you respond "No" to the error message (indicating that you don't want to edit the formula again), and then confirm that you do want to go ahead and save the table change, even though it contains an unverified formula, the formula appears to work correctly. I'm not sure why SQL Server can't verify the formula - may have to do with the mixing of data types (datetime, decimal, float following division, and then whatever you're using for the column type - I happened to use Decimal, but it still barks when I use Float).

Anyhow, give it a try. It seems to work. Maybe someone will read this and offer an explanation.

Good luck.

ewbi.develops | 2006.10.19 01:16 PM

Tons more on extracting the date-only, start/end of week, month, quarter, year etc in my blog post "More on DATEs in SQL" here:


Marc Brooks | 2006.11.15 11:54 PM

Thanks, Marc.

ewbi.develops | 2006.11.16 08:18 AM


This subject hits home for me today. I've been struggling for a while trying to get the date only from a column in an Access table with the column formatted as ShortDate. The Access table only shows dd/mm/yyyy.

When I load the table into an ASP.Net GridView via a SELECT statement, the date is formatted to DateTime by SQL 2005.

How can I use this:
to get the date only.

Thanks for some help!

JON | 2006.12.22 02:47 PM


I think I can help, but I'm a little confused about where exactly you want "to get the date only". You mention Access, SQL Server 2005, .NET's DateTime, and ASP.NET's GridView control. Getting the "date only" (or in the case of the GridView, showing only the date) means different things and is done different ways in each of these environments.

If you can give me a little more info about what you need and where you need it, I promise to take a look.

ewbi.develops | 2006.12.23 04:31 AM

Many thanks for your input!

I'm sorry, in the post above I put dd/mm/yyyy. It should have been mm/dd/yyyy.

A date formatted as ShortDate in Access that looks like this in the Access table, 12/24/2006, winds up like this after it loaded into the ASP.Net GridView, 12/24/2006 12:00:00 AM.

I tried 9 ways to Sunday to get it formatted to mm/dd/yyyy in the GridView. From what I understand, this an SQL thing.

Any ideas?

Jon | 2006.12.24 02:46 PM

Hi Jon,

If I understand you correctly, the problem you have is not with the fact that you have date/time values in your SQL-selected date/time columns (this can't be avoided, as they're represented internally as doubles with the mantissa representing the time, which minimally will be 0 denoting midnight, and this remains the case once the SQL data/time values are loaded into native .NET DateTime values).

The problem you have is you don't want the unused (and, therefore, unimportant) time portions of these date/time values to show in your bound GridView control.

If that's the case, then you need to be explicit about the formatting of your GridView's columns using the Columns element along with asp:BoundField and/or asp:TemplateField definitions. Using asp:BoundField, you can specify a DataFormatString that excludes the time portion of your DateTime values, and using asp:TemplateField you can format your dates anyway you want using (for instance) the second parameter of the Eval method in your data-binding expressions.

Just to be clear, the the SQL described in the post above is only intended to "strip" the non-0 time portion from a native SQL date/time value. It has nothing to do with the presentation of date/time values. If a SQL date/time value will be formatted for presentation without showing the time portion, then the SQL described above isn't even necessary.

Hope this helps. Good luck.

ewbi.develops | 2006.12.24 11:33 PM

Thanks a bundle for your interest and sharing your knowledge.

I stuck this in the asp:boundfield tag and now it works like a charm.

DataFormatString="{0:d}" HtmlEncode="False"

In some circumstances, there may be some security issues with using HtmlEncode="False".

The app works as planned now!

Jon | 2006.12.25 05:13 PM

how to get date in different format with out time through create table query

kothandaraman | 2007.01.20 02:07 AM

I want to compare two dates in view,
I changed the my date coulumn in table to varchar in view.(2007-02-12 :12:15:10:22) to 12/02/2007).
pls help me

Mohamed Ali.k | 2007.02.12 11:46 PM

kothandaraman, unless I'm misunderstanding your question, I don't believe that's possible - the base datetime data type will always include a time portion, even if it is never set to anything.

ewbi.develops | 2007.02.13 09:42 AM

Mohamed Ali.k, not sure I understand what you have or what you want to do. As I understand it, you have a datetime column in a table. You've included that column in a view, but in the view the datetime value is represented as a varchar. Now you wish to compare that varchar date/time value in the view to another datetime or varchar value - in the view, in another table, or something? If you can provide a little more info I'd like to help if I can.

ewbi.develops | 2007.02.13 09:46 AM

i have one quires, actually i give the date as '31/1/2003' it is varchar. i want to convert this date to datetime format. i had tried. but i cant find out solutions. send me the quires.

select convert( datetime, convert(varchar(12), dateofbirth, 111)) from mytable

this my quiery.

Sundaram | 2007.02.27 05:23 AM


I don't have time to figure out exactly what you're looking for, but I suspect you'll find some help here:


Good luck.

ewbi.develops | 2007.02.28 10:30 AM

FROM [EmployeTracking].[dbo].[TimeSheet]
WHERE Convert(nvarchar(20),TodaysDate, 101) = '04/17/2007'
Gives out put in SQ; server
This not:
FROM [EmployeTracking].[dbo].[TimeSheet]
WHERE Convert(nvarchar(20),TodaysDate, 101) = '4/17/2007'
Only 04 converted to 4
hw u compare Date with date from Control in Query?

Abhijit | 2007.04.17 03:27 AM


The expression Convert(nvarchar(20),TodaysDate, 101) returns a string having a particular format, namely 'mm/dd/yyyy'. As a result, any string you compare with that string would have to match exactly, which is why the second query fails.

Assuming your TodaysDate value doesn't include a time portion (which, given the success of your first query, seems to be a safe assumption), you can just let SQL Server take care of converting your date literal to a date for comparison:

FROM [EmployeTracking].[dbo].[TimeSheet]
WHERE TodaysDate = '04/17/2007'

FROM [EmployeTracking].[dbo].[TimeSheet]
WHERE TodaysDate = '4/17/2007'

I couldn't test your exact queries, but here are the equivalent ones I did test:

declare @TodaysDate datetime
SELECT case when @TodaysDate = '04/18/2007' then 1 else 0 end
SELECT case when @TodaysDate = '4/18/2007' then 1 else 0 end

Hope that helps. Good luck.

ewbi.develops | 2007.04.18 02:28 AM

Thank you guys. You've been most helpful. I settled with this code because it is easy to use and to remember:
convert(nvarchar(20), getdate(),103)

Omar Ghazal | 2007.05.20 11:55 PM

I was trying to figure out something similar and I finally figured it out... When selecting a datetime field from a table and you want to remove the time from the date, you don't do it in the Select statement, instead you do it in your form field, for example...

' runat="server"/>

Simon | 2007.06.08 02:02 PM

Oops that didn't come through, here is a portion of the code which could be put in a Label field in Formview...
eval("mydate", "{0:d}")

Simon | 2007.06.08 02:05 PM

Hi Simon,

Thanks for the comment. Sorry TypePad ate your code snippet.

What you point out is certainly true in many cases. If you're only interested in suppressing the time portion of a date/time value during presentation (on-screen, in reports, exports, etc.), then there are as many ways to do it as there are development environments. If, on the other hand, you're interested in executing comparative SELECTs against date/time values without consideration for their time portions, or perhaps performing SP- or SELECT-based date math and you need to factor out (or round up/down) the time portions, then one or more of the approaches described above will be needed.

ewbi.develops | 2007.06.08 02:18 PM

I am in the process of learning SQL 2005...In a select statement where you want to return several column/fields including a datetime field, where exactly in the select statement would you use the convert code?

Mitch | 2007.06.24 07:21 AM

Hi Mitch,

That's a pretty broad question. I suppose the short answer would be anywhere you want a date and no time value. This might be in the SELECT columns, if you want to present a datetime value without the time portion, or it might be in the WHERE clause, if you want to test a date against a datetime value without consideration for the time portion.

Suppose you have a table named MyTable with columns for ID, Name, and LastUpdate, where LastUpdate is a datetime value. If what you want to do is select the table's columns, but leave out the time portion of LastUpdate while still returning it as a datetime value (as opposed to a varchar or other string-based value), you can do this:


If instead what you want to do is select the rows whose LastUpdate value falls on a particular day, without consideration for the time, you can do this:


However, that's probably not the best choice, as it would ignore any index on LastUpdate. The better choice for this type of select would probably be something like this:

SELECT * FROM MyTable WHERE LastUpdate >= '6/25/2007' and LastUpdate < '6/26/2007'

Anyhow, hope that helps. Good luck.

ewbi.develops | 2007.06.25 09:41 AM

DATEADD(dd, DATEDIFF(dd,0,@DateTime), 0)

What a great line of SQL !

Thanks for a lot less hassle !

I was coding it in VB (Now.Date) and was oblige to have every date passed in parameter !
Thanks a lot !

Nicolas Brassard | 2007.07.04 07:31 PM


I've tried using your queries:
and I still get back a FULL datetime formta in MS SQL 2005.
I know I could just use:
but this may give me weired results during sorting.

Is there a way to preserve the 'datetime' format needed for sorting while displaying only date-portion in SQL 2005?


Anna | 2007.07.09 11:54 AM

Hi Anna,

The SQL queries described in my post above (and some of the queries in the comments) are meant to return date/time values in native DATETIME format for proper WHERE and ORDER BY clauses. As you've discovered, this is not the same as converting date/time values to strings (VARCHARs) for presentation. In your case, if I'm understanding it correctly, you need both: a native DATETIME value for sorting, and a specially formatted VARCHAR for presentation. So, if you have a table named mytable with a DATETIME column named mydatetime, just use a different CONVERT for each:

CONVERT(VARCHAR, mydatetime, 101)
FROM mytable

Hope that helps. Let me know if I've misunderstood your question. Good luck.

ewbi.develops | 2007.07.09 12:46 PM


If you have a GETDATE() result like '10-10-2007 18:00:00', with this method it will be rounded to 11-10-2007 00:00:00' due to 18:00:00 being more next to 0h of next day.


Vítor Lopes | 2007.10.10 08:42 AM

Vitor, I described both of these approaches in the original post above three years ago, showing the non-rounding FLOOR version first and explaining the behavior of the INT-rounding approach second. I don't believe it is accurate to simply say that the second approach should not be used. Folks should use whichever approach provides the behavior they are interested in achieving.

ewbi.develops | 2007.10.10 09:48 AM

Regarding this approach:

SELECT DATEADD(dd, DATEDIFF(dd,0,MyDateField), 0)

I just tried


to insert records to a DATETIME field which I want to hold only date, and it loads correctly.

Is there some reason people don't do it this way? It would seem more efficient. Would the use of the CONVERT function as you suggested at the top be more efficient yet?

René Valencourt | 2008.02.28 10:50 AM

An update to my previous post:

I can see that the first operation results in a normal datetime field with time of midnight, and the second one results in the numeric value for the day.

E.g., 2008-02-28 00:00:00.000 vis-à-vis 39504.

They both work fine when inserting a record to a datetime field.

I would also guess that it would be about the same efficiency as

cast(floor(cast(MyDateField as float) as datetime)

as I would guess that the date substraction for a date in days format might be implemented the same under the covers.

René Valencourt | 2008.02.28 12:58 PM

René: yes. :)

ewbi.develops | 2008.02.28 01:08 PM

here is your answer for using without time only date

CONVERT(varchar(20),CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, InGate.dateIn))), 101) as dateIn

suleman | 2008.04.25 12:04 AM

and u can also use this

CONVERT(varchar(20),InGate.dateIn, 101) AS dateIn

salman | 2008.04.25 12:06 AM

how to convert datetime into 12 hours format???
i can't have any idea to convert date into 12 hours format.if any one have idea than please mail me...or give some idea how to convert??

Yogesh | 2008.05.22 11:54 PM

its really very helful to me.

TeJal | 2008.07.03 05:29 AM

to convert datetime to ineger

ephron | 2008.07.30 11:20 AM

Thanks, helped me

Suneetha | 2008.08.06 11:09 PM

when i convert Present Date in vb.net
like this:-
Dim Currentdate As DateTime = Date.Today
this will return mm/dd/yyyy
but this will use in my Query,
then again it returns as
dd/mm/yyyy. why?
in Query i used variable type as Datetime ,is this problem

bharath | 2008.08.13 10:54 PM


I think you might be mixing different things. There's no question that internally dates are managed properly, including implicit and explicit conversions between .NET DateTime variables and database DATETIME values. However, when you say "will return", I assume you are talking about the presentation of a date value, and that's a different thing. You can apply formats to your .NET DateTime variables and to your database DATETIME columns when converting them to strings for presentation, this will allow you to get whatever type of presentation format you want: mm/dd/yyyy, m/d/yyyy, dd/mm/yyyy, etc.

Hope this helps. Good luck.

ewbi.develops | 2008.08.13 11:08 PM

Thanks for this post, it was helpful

Mahi | 2008.09.19 03:58 AM

Even easier than all the rest (removing the time part):

Christoff Smith | 2008.10.21 02:11 AM

cool.. i like DATEADD, much faster query

slare | 2008.11.25 08:35 AM

for getting timeformat of 12 hrs, try this
Select timeonly= ltrim( right( convert(26),column_name,109),14))

Murali | 2009.04.01 11:10 PM

If u want date in dd-mm-yyyy format then write
select convert(varchar,datecolumn,105)as cdate from tablename ....

Vinay | 2009.06.30 05:25 AM

the current format of the date going into the tables is say 2009-07-06 17:23:12. How to convert it into only date ie 2009-07-06 00:00:00, and how to ensure that in future, only the date goes into the tables with minutes values being at 0

manish dalal | 2009.07.08 11:13 AM


I class myself as a bit of a novice when it comes to writing SQL convert or cast statements so need some help with this one!!

I have a value in the database which is a float value (for example 39838.4931597222) which in the front end displays the date/time (e.g. 27/01/2009 11:50:09). I write an update script to amend this float value so that the value is 30/12/2007 00:00:00, for example. I guess what I need to do is figure out how to convert the date/time into the float value.

Can you help?!

Russ Webb | 2009.08.20 02:44 AM

Hi Russ,

The syntax for converting between datetime and float data types is fairly straightforward:

select CAST(GETDATE() as float), CAST(40043.1201974537 as datetime)

That said, I'm wondering why you might be storing legitimate datetime values as float in the database anyway, instead of just as datetime?

Good luck!

ewbi.develops | 2009.08.20 02:58 AM

That worked a treat for todays date, however what i want to do is something along these lines:

select CAST('2009-01-27 11:50:08.997' as float)

When I try this I get an 'error converting data type varchar to float'.

Apologies if my noviceness is frustrating you. As for the way the data is stored, it's not mine i've just been tasked with sorting out some inaccurate data!

Many thanks for your help in advance!!


Russ Webb | 2009.08.20 04:06 AM


Please ignore my last comment.

I've been playing about with the float number and figured out the number i need to use. I need to update multiple fields with one date, as a float value. The date I need is not necessarily specific, so long as it is in December 2007. I've figured I can just replace the existing float value with the float vaule 39444.0000000000 to get it to display on the front end as 2007-12-30 00:00:00.000. Job done!

Thanks for your help earlier, it was very much appreciated!


Russ Webb | 2009.08.20 04:51 AM

Glad you figured out what you needed.

I didn't realize that you needed to convert a string to a datetime value before converting it to float. There are some options for converting string values having different date/time formats, but the format you've given is the default and SQL Server has no problem interpreting its parts, so you can just CAST it to a datetime type and then to a float:

select CAST(CAST('2009-01-27 11:50:08.997' as datetime) as float)

That returns 39838.4931596836. Good luck!

ewbi.develops | 2009.08.20 09:51 AM

God bless him/her who put this help

M T Khan | 2009.08.20 01:43 PM


Duwayne | 2009.09.10 11:21 AM

Hi Duwayne,

Glad you mentioned this - some folks may not be aware of the separate DATE and TIME data types introduced with SQL Server 2008:


ewbi.develops | 2009.09.10 11:32 AM

thank you so much for the code in converting long date to short date.... ive been searching for almost a day from lots of website... but it was only your code that worked for me...

Hazel | 2009.09.10 11:31 PM

can any1 please tell me how to add 15 min to current_timestamp
i.e if current_timestamp is 2009-09-25 16:30:00.000 want to add 15 min to it 2009-09-25 16:45:00.000

Jinal | 2009.09.25 04:09 AM


Assuming you are talking about a DATETIME value, see the DATEADD function:

select GETDATE(), DATEADD(mi, 15, GETDATE())

If you are talking about a TIMESTAMP value, I don't know that it is possible. From the BOL: "The timestamp data type is just an incrementing number and does not preserve a date or a time."

ewbi.develops | 2009.09.25 09:31 AM

Very helpful, thanks!

Mark | 2009.12.16 09:33 AM

This is great. I also have always the date parsing, but not any more.

Thanks a lot


Ned | 2010.03.03 11:48 AM

how do iget the below display

Mon, Apr11 2010 ,11 AM

raul11 | 2010.04.14 12:06 AM

i hv a filed creat_date(DATETIME),table name 'emai'.i want only date from 'creat_date'.wat wi be the query??

nivi | 2010.06.07 10:09 PM

nivi, per the original post above, try this:

select CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, creat_date))) from emai

ewbi.develops | 2010.06.08 02:53 AM

Use ApplyFormatInEditMode="True" in grid view

Praveen | 2010.09.18 12:47 PM

I just asked myself, because I have a conversion error on way back to the db when textbox gets loaded with "1/1/1900 12:00:00 AM" . . . why are all these columns 'DateTime' . . . why are they not just defined 'Date' ???? Problem avoided !! Your thoughts . . .

xpxj | 2011.05.10 08:11 PM

xpxj, the DATE data type is a fine solution. However, it (and the TIME data type) were introduced with SQL Server 2008, four years after this blog post was written. Also, sometimes, folks need to store full DATETIME values, but are then faced with needing to manipulate and/or present just the date portion. This post addresses that need as well. Thanks for the comment.

ewbi.develops | 2011.05.10 11:29 PM

Hey how do i acccess only the date part from a column say dat of birth
am developing an asp.net application

Vini Katyal | 2011.06.15 10:04 AM

Vini, that's a very broad question. You will need to supply a lot more information for anyone to answer it. For instance, how are you accessing the data (ADO.NET, Linq to SQL, Entity Framework, etc.)? What are you doing with the data once you retrieve it, loading/binding the columns to class properties? Will you have the db's DATETIME column in a DateTime-typed variable? Are you auto-binding your db results (recordsets, class lists, etc.) to ASP.NET web form components (grid, textboxes, etc.)?

ewbi.develops | 2011.06.15 10:15 AM


TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83451dcb869e2

Listed below are links to weblogs that reference SQL Server Date-Only from DATETIME:

» SQL Server Date-Only from DATETIME from Dewayne and Shadow his Webdog
Link: ewbi.develops: SQL Server Date-Only from DATETIME. SQL Server Date-Only from DATETIME As one of the messages in this thread suggests, the question of how best to return only the date portion of a DATETIME value in SQL Server, specifically [Read More]

Tracked on Dec 8, 2004 10:28:09 AM