Getting Only the Date Part of a Date/Time Stamp in SQL Server

I'm just wondering, can't you simply do a DATE(dateTimeStamp) to get the date part of a date/time stamp field?

The best way to go about this problem in MSSQL is CAST(DATEADD(DAY,0,GETDATE()) AS DATETIME)
Actually, what we want to do is THE_DATE BINARYAND FFFFFFFF00000000

I am using MS SQL 2005 Server
I tried BEN's way and the other 'best' way = CAST(DATEADD(DAY, 0, GETDATE()) AS DATETIME)

But none of them works. I am developing a VB .NET application. I think I have to use the

.SubString(0, .IndexOf(" ")

function to get the DATE only part. It's slow, but haven't found any other way yet. Thanks all.

Mehdi in VB.NET you can try following:-
1. DateTime.Today
2. DateTime.Date
The above will return a DateTime object withe value set to the today's date and time part set to 00:00:00.

BUT if you need to display without the the time part then use:-

DateTime.Today.ToString("yyyy/MM/dd") or
DateTime.Date.ToString("yyyy/MM/dd")

You can change the format as you like; check out the MSDN article for DateTime.ToString method.

thankx 4 such a neat idea.
it really works with highest efficiency.

I tried CAST(DATEADD(DAY,0,GETDATE()) AS DATETIME) but the time values are not set to 00:00:00 (using SQL Server 2000).

If you are using any flavor of MS SQL server, it seems like you are making this problem way more difficult than it needs to be.

It has been my experience that when most people ask for this they want to use it for display to a user or in some report, so you can safely convert to a string representation. The storage of a DATETIME in SQL will always result in a date and time value (default 00:00) whether or not you pass the time, so removing it prior to storage has no effect.

The built-in CONVERT function, however, allows you to take any date format and return a string representation of just the date portion thusly:

SELECT CONVERT(nvarchar(20), GETDATE(), 101) AS JustTheDate

The Syntax:

CONVERT([datatype], [date value], {format code})

There are several 'format codes' used in MS SQL to pull out parts of the date for your locale and are documented on MSDN. A few examples are: 103 = dd/mm/yy77, 107 = Mon dd, yyyy or 112 = yyyymmdd.

Very helpful, Ben. I, too, needed just the date within SQL.

I tried CASTing as INT and it seems to work fine. Saves from using FLOOR().

Thanks again - Ben

OK these methods are all fine, but there is one overlooked problem. I wrote a function called DateOnly that encapsulates this time stripping functionality so I don't have to do it over and over.

The other nice thing about a function is it can be used to add a calculated column to a table so you can have one column, let's say OrderDate, that stores the actual date and time, but put a calculated column, call it OrderDateOnly, right next to it, so you can query that column with a regular equals comparison instead of doing a date range.

ALTER TABLE Orders
ADD OrderDateOnly as dbo.DateOnly(OrderDate)

The problem with that is that because it's doing a calculation on a column, it would not be able to use an index on OrderDate to do this query and it would result in a table scan. However, calculated columns may be indexed just like stored columns, but only if the column is deterministic. This is a property that guarantees the function always returns the same value for a given set of parameters. The problem with any solution that uses Convert or Cast is that it makes the function non-deterministic, so a create index would not work.

CREATE INDEX ixOrders_OrderDateOnly on Orders(OrderDateOnly)

The only way I have been able to make a function that is deterministic such that it can be indexed requires the use of the DateAdd function to strip off the hours, minutes, seconds and milliseconds from the date, one at a time. It makes for a messy looking query, but it works.

Here's the final Function.

CREATE FUNCTION DateOnly(@date)
RETURNS datetime
WITH SCHEMABINDING
as
return
DATEADD(ms, -1 DATEPART(ms, @date),
DATEADD(second, -1 DATEPART(second, @date),
DATEADD(minute, -1 DATEPART(hour, @date),
DATEADD(hour, -1 DATEPART(hour, @date), @date)
)))
go

I've not compared this performance wise to the other functions, but I know that in any case where I want to create an indexed calculated column, that alone will outway any other performance issues that might occur on the calculation part alone.

If you're working w/ MSSQL and doing anything with dates, DATEPART is a savior.

Believe me.

Ok, I beleive in you :: DATEPART is the Savior.

Whatever I do to a DATETIME field, in .NET application it automatically adds 00:00:00 for the time. Ofcourse, we can use client side programming function to convert the DateTime data type to To String() type (From my .NET experience). OR, use DATEPART and create a nvarchar type data on SQL Server side.

My table has "AddedON" as DateTime column. Here is my SQL Select Command:

SELECT FolderName, Convert(nvarchar(2), DATEPART(mm,AddedOn)) + '/' + Convert(nvarchar(2), DATEPART(dd, AddedOn)) + '/' + Convert(nvarchar(4), DATEPART(yyyy, AddedON)) AS AddedOn, (SELECT Count(FolderName) FROM InventoryTable
WHERE InventoryTable.FolderName=NewReleasesTable.FolderName) AS FileCount FROM NewReleasesTable WHERE AddedOn >= (GetDate()-@Range) ORDER BY AddedOn DESC

Another way would be, make whole datetime to a string then split on the blank (seperating date & time).

Here is the result shown:
http://bangla.homeip.net:8000/NewAlbums/index.aspx

i have a third party app where all dates are stored as numeric in yyyymmdd format on sql server 2000.

i can't ever do any kind of math on these dates. i try casting and converting to no avail. while i am a novice to begin with, i am a total noob with dates. i would just like to be able use things like DATEADD without getting:

Arithmetic overflow error converting expression to data type datetime.

i've googled my heart out, what can i do?

Not sure what your problem is. I use that date format all the time. If you convert(datetime, colname) it will return a datetime as long as the data you supply it is proper. Of course, if you are supplying the number as an integer, it will not return the result you expect. You must pass it as string formatted as YYYYMMDD. An integer would be interpreted as that many days since 1900-01-01, which may be the cause of your overflow. I think if you are storing the dates as numerics, you need to cast them first to varchar, then to datetime, like this:

convert(datetime, convert(char(8), colname))

If you still have bad dates, try using the ISDATE function to only convert legal dates and filter out the bad ones.

The UDF contains a small error. I couldn't create it. I had to replace the '-1' with just a '-'. And then I discovered it always results in a datetime with the time set to '01:00:00'
So it should be:

CREATE FUNCTION DateOnly(@date)
RETURNS datetime
WITH SCHEMABINDING
as
return
DATEADD(ms, - DATEPART(ms, @date),
DATEADD(second, - DATEPART(second, @date),
DATEADD(minute, - DATEPART(minute, @date),
DATEADD(hour, - DATEPART(hour, @date), @date)
)))

hi,
How can i write a custom type myDate in sql 2005?

-- Create the data type
CREATE TYPE myDate
FROM base_type,,nvarchar (precision,int,25) allow_null,,NULL
I am unsure how to use this. am i looking in the right direction?

I also run into the date/time problem on SQL Server. I have solved the problem in several ways. When possible I simply create the table column with a check statement that prevents time from being added:

create table test
(
dateonly datetime null constraint ckc_dateonly check (DateOnly is null or (dateonly = cast(convert(varchar(8),dateonly,112) as datetime)))
)

When inserting data and you want to use getdate() simply use
cast(convert(varchar(8),getdate(),112) as datetime). (It can be used as the default value for the column also).

I never found a good way around the indexing problem for computed fields (though I do use those also):

create table test
(
DateWithTime datetime null,
DateOnly datetime null constraint ckc_dateonly check (DateOnly is null or (dateonly = cast(convert(varchar(8),dateonly,112) as datetime))),
ComputedDateOnly as (cast(convert(varchar(8),datewithtime,112) as datetime))
)

Of course you can also create a real column "DateOnly" of datetime and have it updated via a trigger when ever an insert/update is done that changes the associated base column. This would be like a computed column but you could then index it however this is always the chance of something getting out of whack.

Sorry I have not been following this thread for a while. Somehow when I posted the source code of the UDF the stars disappeared. It is supposed to be -1 * in there, but as the subsequent comment shows a plain minus sign works well also. In any event, this is the only form of a date correction function I have ever been able to create that will actually be deterministic, such that it is indexable. Using this function to create a date only calculated column is by far the most efficient method of making an index seek on the datetime column. The use of a secondary physical column which is maintained through triggers is not recommended, as triggers greatly impair performance, and the second physical column increases storage space which might also lead to more data pages being required.

The best method is still to just have an index on the regular datetime column itself and use a range query in the form of:

WHERE datecol >= startdate and datecol < startdate + 1

but this is not intuitive to the casual user creating adhoc reports. So in a table in which I actually want to store the time portion, I would include the calculated column using the UDF and also create an index on the dateonly column if it were going to be a column that is frequently used in a WHERE clause. In cases where I don't want a time actually stored, I would correct it before saving it using the UDF to strip off the time. I would typically do this in the stored procedure that adds the records. Or in an extreme case where I have no other choice, possibly a trigger.

Hey guys,
Just thought I would post about this. I got around this problem very easily, this is the route for a non-programmer.

declare @mydate datetime SET @mydate = (Getdate())
declare @mymonth varchar(2) set @mymonth = month(@mydate)
declare @myday varchar(2) set @myday = day(@mydate)
declare @myyear varchar(4) set @myyear = year(@mydate)

declare @Totals varchar (20) s
et @Totals = (@mymonth + '/'+ @myday + + '/'+ @myyear)

Then just query with @totals you can play with the datatypes etc, but this is the quick and dirty

Here is my code. Works okay but the last = statement returns an error. How to I compare the newreportdate and the JustTheDate values. I have tried many maybe to many ways.
Thanks
Landon

SELECT reportdate,worktype, maintby,

CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )as timeret,
CONVERT(nvarchar(20), GETDATE(), 102) AS JustTheDate,
CONVERT(nvarchar(20), reportdate, 102) AS newreportdate
from workorder
where worktype='pm' and maintby ='em' and newreportdate=JustTheDate

Landon,

you can solve your problem with the nested select:

select *
from
(SELECT reportdate,worktype, maintby,
CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )as timeret,
CONVERT(nvarchar(20), GETDATE(), 102) AS JustTheDate,
CONVERT(nvarchar(20), reportdate, 102) AS newreportdate
from workorder
where worktype='pm' and maintby ='em') _temp
where newreportdate=JustTheDate

Hai Friends,

i have one doubts in SQLSERVER Query,

i need to extract only TIME by using Sql Query.

for eg. 10:30am, 11:30am, 12:30pm.

can you give me solution anyone?

Thanks & best Regards,
Mani

try this: (date is 24hr)

declare @MyDate datetime
set @MyDate = getdate()

Select
rtrim(datepart(hh, @MyDate)) +'h'+
Case len (datepart(mi, @MyDate))
when 1 then '0'
when 2 then ''
End +
rtrim(datepart(mi, @MyDate)) +'m'+
rtrim(datepart(ss, @MyDate)) +'s'
as 'Time'

Garth had it right, at least with SQL Server. You don't need a custom function to do what's already built in.

If you need to use it in a comparison you can easily do something like

WHERE CONVERT(nvarchar(20), MyDateColumn, 101) BETWEEN @DateFrom AND @DateTo

I always forget the syntax though and have to look it up again...lol

I know I'm late to the party, but having dealt with both SQL Server and Oracle at length, it's easy to say that date handling and manipulation in SQL Server leaves much to be desired. As evidenced by this thread, truncating dates in SQL Server oftentimes involves massive amounts of function nesting and string concatenation. Oracle offers a function named "trunc", which does the same thing in a neat little package. The group I work for oftentimes deals with massive amounts of data, so we are all the time summarizing that data by day/month/quarter or even year in some cases. Remembering all the hoops to go through is just a mess, so I wrote a SQL Server equivalent to Oracle's TRUNC function.

The function works like this:

fn_trunc(p_date,period)

For instance, if I want to truncate getdate() to today's date:
select fn_trunc(getdate(),'d')

or the first day of this month:

select fn_trunc(getdate(),'m')

.. and so on. fn_trunc will truncate any date to the nearest minute, hour, day, month, quarter or year. The period argument accepts the same arguments the SQL Server DATEPART function uses, as well as the equivalent arguments used in the Oracle TRUNC function.

I'll paste the code below, and please note that this implementation is for a system-wide function. Also note that I have not exhaustively tested the performance of this function call, so please anyone chime in if you have discovered a quicker way to skin this cat. I stayed away from string concatenation where possible, but prefer that over things like multiple DATEPART calls.

<code>
USE master
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE uid = USER_ID('system_function_schema')
AND name = 'fn_trunc')
DROP FUNCTION system_function_schema.fn_trunc
GO

/* ALL SYSTEM-WIDE FUNCTIONS MUST BEGIN WITH "fn_""AND
MUST BE ALL lowercase
*/
CREATE FUNCTION system_function_schema.fn_trunc
(
@p_date datetime,
@p_period varchar(4) = 'd'
)
returns smalldatetime
as
begin
declare @l_date smalldatetime

set @l_date =
case
when @p_period in ('n','mi')
then cast(convert(varchar(16),@p_date,120) as smalldatetime)
when @p_period in ('h','hh')
then cast(convert(varchar(14),@p_date,120)+'00' as smalldatetime)
when @p_period in ('d','dd')
then cast(cast(@p_date as varchar(11)) as smalldatetime)
when @p_period in ('w','wk','ww')
then dateadd(dd, 1 - datepart(dw, convert(varchar(10), @p_date, 111)),convert(varchar(10), @p_date, 111))
when @p_period in ('m','mm')
then cast(convert(char(6),@p_date,112)+'01' as smalldatetime)
when @p_period in ('q','qq')
then cast(cast(year(@p_date) as varchar(4)) + '/' + cast(datepart(q, @p_date) * 3 - 2 as varchar(2)) + '/01' as smalldatetime)
when @p_period in ('y','yy','yyyy')
then cast(convert(char(4),@p_date,120)+'-01-01' as smalldatetime)
else
cast(cast(@p_date as varchar(11)) as smalldatetime)
end

return @l_date
end
go

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
</code>

I still don't know what Floor is, but Floor/Cast worked for me.

Thanks, Ben!

yikes, use this, and drop all that cast and floor stuff.

create function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
go

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

These methods may be more elegant or perform faster but my original formula of subtracting the hours minutes and seconds is still the only one that will result in a deterministic function that can be used to create a calculated column which is indexable. I cannot conceive of a situation where you would use this function so many times that millisecond differences in performance matter except if you are using it to query from a large table. If that is the case, the performance would be best if the table were already indexed with the calculated value. The original premise of the function was to enable people to write a query to find all records that occur on a date when the datetime field has both date and time.

SELECT * FROM table WHERE date >= date1 and date < DateAdd(day, 1, date1) is the best option in the absence of the DateOnly function, but a casual user would not know to do this.
SELECT * FROM table WHERE dateonly = date1, where dateonly is a calculated column in the table and indexed, would give about equal performance to the first query, but is simple enough that a user of Crystal Reports
would intuitively use it.

I agree that performance is extremely important and that is why I spent days finding the best formula, not just based on an isolated unit test of performance, but on the totality of its use.

I would love to find another solution to this function that would result in ObjectProperty('DateOnly', 'IsDeterministic') to be true, but I have not yet found one. Any function that uses conversion to another datatype loses determinism.

Since you would only have to write this function once, even though it's ugly, it still makes the most sense to use the method of removing the hours, minutes and seconds through DateAdd and DateDiff.

I don't mean to be argumentative, I just want to share with everyone something that I found to be one of the most difficult problems I've ever had to solve in SQL Server. Determinism is basically a property that says a function will always return the exact same value for a given set of parameters. It is essential that this be true in order to build an index on a calculated column. UDF's that involve rounding or data conversion are generally not deterministic. This DateOnly function was a particularly difficult one to make deterministic. I tried dozens of different formulae (pretty much every other version you see listed here on the site and then some). Ultimately the only one I have found is the one involving subtracting the hours, minutes and seconds. I shared it with everyone because it was so frustrating to get to that solution.

I believe performance is always important, but as I said, unit test performance on the various formulae in isolation is not a true test of the overall practical performance. Ultimately having an indexed column to retrieve the matching rows is the fastest technique. Indexing on the base datetime column and doing a range query is the most efficient because it does not involve the use of the function at all. But because of the complexity of doing the range query properly, the casual SQL user will often get the wrong result or write an inefficient function of their own to fetch rows that happen on a day when using a datetime column that has a time portion in it. So the genesis of this function was to simplify that for them. If you are not concerned with storing the time in the field and simply want to fix the datetime column to midnight by using a default on the column, then I would argue it would be better to use one of the functions that calculates the fixed datetime value the quickest, since determinism would not be necessary for that function. Ideally if I had to do both of these scenarios, I would probably opt to have two different implementations of this function DateOnlyD (deterministic) for the one I use for calculated columns that will be indexed, and DateOnly for ones that I would use for Default constraints or any other case that determinism is not needed for indexing.

A Great post but on trying to implement it on SQL Compact 3.1 CAST is not supported but you pointed me in the right direction of

CONVERT (datetime, FLOOR(CONVERT (float, GETDATE())))

(.NET (Bracket (Matching (Optional)

Hi, this question is for Joseph Gagliardo.

I am trying to write a deterministic function in sql server 2005. I intend to use it in a persisted computed column.

The functions I am working on convert RGB color values to HSV and to Hex. However, this is sort of beside the point.

None of my attempts have turned out to be deterministic. I've been creating all these test functions to try to figure where "the line" is for making a deterministic function. Even this:

CREATE FUNCTION TestFunc
()
RETURNS int
AS
BEGIN
RETURN 9;
END

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.TestFunc'), 'IsDeterministic');

is giving me zero!

What am I doing wrong here?

I cannot remember all the details off the top of my head, and I am not near a SQL Server at the moment. But I remember there being a bunch of requirements to make a function deterministic. I think it had to be schema bound, and there were a few environment settings that had to be a certain way, like QUOTED_IDENTIFIER and stuff like that. I am sure you can find some documentation on the requirements for it in books on line or the web, if not, I will try to dig it up in my notes. It's one of those things I do so infrequently I forget some of the details.

I know it is very particular though, and using things like convert and floating points and stuff like that don't work. Actually as I write this you are making me think that I might need to test the function that used the datediff because the quick test I did may have not been deterministic because I might have forgotten some of those requirements. I would be absolutely thrilled if I could find a formula that is easier than the one I've been using but is also deterministic.

Thanks for making me think about this, I will follow up on it tomorrow and let you know what I find.

Awesome! Thanks a tonn. I generally tend to do this kinda things in app layer c# etc.. but sometimes there is no choice but to use tsql. Saved me quite a bit of time.

Yup, it was the schema binding! I added WITH SCHEMABINDING and it made all my functions deterministic.

In SQL 2005, using floats makes the function non-precise. That is to say, the IsPrecise property will be 0. However, a function can be non-precise but still be deterministic! However, I'm not sure if precision affects determinism in older versions of SQL server.

Nice. Clear, Easy and suggestions

I have another simple solution for separating out time from the datetime stamp.

convert(datetime,( convert(varchar (11),getDate())))

Thank You .....

To strip time off of datetime values to store only the day, I usually just use this approach

declare @DateValue datetime
set @DateTest = getutcdate()
select convert(datetime, convert(int, @DateTest))
- or -
convert(datetime, convert(int, <DateColumn>))

To store only the time, it is a little trickier, but not much:

declare @DateValue datetime
set @DateTest = getutcdate()

select convert(datetime, convert(decimal(19,12), @DateValue) - convert(int, @DateValue) + 1)
- or -
select convert(datetime, convert(decimal(19,12), <DateColumn>) - convert(int, <DateColumn>) + 1)

Note: The decimal(19,12) gives you the full valid range of the DateTime datatype without any loss of precision. If you don't need that level of precision, you could go less.

-T.

Joseph,

You wrote last fall:

>> I know it is very particular though, and using things like convert and floating points and stuff like that don't work. ... I would be absolutely thrilled if I could find a formula that is easier than the one I've been using but is also deterministic.

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

in converting a date to a floating point puts the time in the decimal portion. "Floor" strips away the decimal portion, and the final cast converts that to a datetime with a time of midnight, since the decimal portion of the number had been set to zero. This is deterministic, and in my experience HAS always worked.

I think that it is also probably about as efficient as you can get, although

dateadd(day,0,datediff(day,0,MyDateField))

may under the covers perform the same operations.

Thank you thankyou thankyou. What a pain in the butt. I had to get the invoices sent between 2 specific dates and the hours were really slowing things down... expecially the conversion back and forth for the criteria.

I actually change the structure of the view I was using to draw data from in my web report to:

select clientname, Invoice_id,
CAST(
FLOOR( CAST( invoice_date AS FLOAT ) )
AS DATETIME
) as invoice_date, .......

Then I could do my select statement:

"where ( rs.invoice_date between '" & begin_date & "' and '" & end_date & "') and office_id = " & office_id & " order by office_number, invoice_id "

and will later put it into a sp.

It increased my time by quite a lot. I was practically read to add a new column to the datatase! Converting to varchar and then back to a date and then running the "between" statement was simply awful.

So, thanks again. Very original and simple.

Oops, I just noticed a typo in my post from February 28...

I posted

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

when I meant

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

Thanks for this information on how to get the date part only out of the date/time stamp, I looked everywhere for this and you r solution is the only one that worked.

Last September Joseph wrote:

>> Determinism is basically a property that says a function will always return the exact same value for a given set of parameters. It is essential that this be true in order to build an index on a calculated column. UDFs that involve rounding or data conversion are generally not deterministic.

In February I wrote:

>> "cast(floor(cast(MyDateField as float)) as datetime)", in converting a date to a floating point, puts the time in the decimal portion. "Floor" strips away the decimal portion, and the final cast converts that to a datetime with a time of midnight, since the decimal portion of the number had been set to zero. This is deterministic, and in my experience HAS always worked.

I must retract and eat my words... :( It does not work if you actually attempt to create an index. I spoke from a theoretical point of view, not from a SQL Server point of view.

SQL Server does not analyze what you are actually doing, to determine whether or not the results should be deterministic (as in my example above). It just makes a blanket a priori determination that if you use the cast/convert operation on a date / float, that the formula will not be deterministic, even if you can demonstrate mathematically for your particular operation that it would be deterministic, as it would for what I showed above, or for this one:

cast( cast(cast(MyDate as float) as int) as datetime)

The definition of "deterministic" that Joseph provides -- always returning the same value for the same set of parameters -- is essentially the one that Microsoft uses, but does not implement precisely.

Thank you Joseph for your in-depth analysis and contribution.

René

I believe Garth had the correct answer. All you need to use is the correct format code. Perfect!

I've read a lot of comments and the main pieces of advice I have are:
- Avoid strings, they're slow
- Some operations take longer than others
- 2 quick operations can be faster than 1 slow operation
- Although "an integer can be used as a date" the database engine still has to do an 'implicit' CAST or CONVERT
- Usining implicit CAST or CONVERT saves typing, not processing time

The fastest answer I have seen is the following:
SELECT
DATEADD
(
DAY,
DATEDIFF(DAY, 0, @date),
0
)

Although using INT or FLOOR to remove the timepart was nearly as fast in my experience.

Getting the Time only is similar:
SELECT
DATEADD
(
DAY,
-DATEDIFF(DAY, 0, @date),
@date
)

Mat.

WOW - That's the slickest version yet

I always used to use the CONVERT(SmallDateTime, FLOOR(CAST(@dtDate as float)), 103) method

The CAST as INT would also work (because of the FLOOR() function)

but your DateAdd / DateDiff method not only runs quick, is fully Deterministic, can work with both date and time parts, and as a bonus takes up less space on screen ;-)

Thanks

P.S. This has been one of the best SQL blogs I've read in a while - Thanks all.

I agree. That is the slickest solution.

I actually just added it to my little toolbox of functions.

CREATE FUNCTION fnShortDate(@date datetime) RETURNS datetime
AS
BEGIN

declare @dateback datetime

SELECT @dateback=DATEADD(DAY,DATEDIFF(DAY, 0, @date),0)

return @dateback
END
GO

so now I can just use:

select dbo.fnShortDate(getdate())

just an excellent solution, thanks very much.

FWIW, I have found in my testing two things:

<ul>
<li>Any data type conversion is much slower than the dateadd method.</li>
<li>Encasing this functionality inside a function (see my trunc function above) results in much slower processing as opposed to just writing out the whole messy dateadd stuff every single time.
</ul>

As such, I have abandoned use of my trunc function. Even though it saves a boatload of redundant syntax, the speed difference is insurmountable when running any sort of date truncation on large tables.

Did I mention that I hate SQL Server's date handling methods?

YMMV

Hmm, thought I could use unordered lists here... my bad!

Thanks its very good 1

You can use SELECT CONVERT(CHAR(10),GETDATE(),103) from

TABLENAME

You will get month/day/year format. If you want different format change 103 to 101 or some other from 101 to 113

You can also cast to binary, and rely (which is what you do when using float, only there you do it implicitly) on SQL Server's spec that defines it that datetime stores the date in the first 4 bytes and time in the last 4. Casting to binary(4) truncates the higher bytes (date) so we get the time only. Moreover, the same method it is applicable with smalldatetime, only casting to binary(2) (trying to use real instead of float to use with datetime won't work because real is signed but smalldatetime is unsigned).

Hope you like this idea. If you have a better name for these functions I would be glad to hear. Here is sample code which creates user-defined functions and tests them a bit.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlyDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlyDate]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlyTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlyTime]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlySmallDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlySmallDate]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_OnlySmallTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_OnlySmallTime]

CREATE FUNCTION fn_OnlyDate(@dt AS datetime)
RETURNS datetime
AS
BEGIN
RETURN @dt - CAST(CAST(@dt AS binary(4)) AS datetime)
END
GO

CREATE FUNCTION fn_OnlyTime(@dt AS datetime)
RETURNS datetime
AS
BEGIN
RETURN CAST(CAST(@dt AS binary(4)) AS datetime)
END
GO

CREATE FUNCTION fn_OnlySmallDate(@sdt AS smalldatetime)
RETURNS smalldatetime
AS
BEGIN
RETURN @sdt - CAST(CAST(@sdt AS binary(2)) AS smalldatetime)
END
GO

CREATE FUNCTION fn_OnlySmallTime(@sdt AS smalldatetime)
RETURNS smalldatetime
AS
BEGIN
RETURN CAST(CAST(@sdt AS binary(2)) AS smalldatetime)
END
GO

-- Check one datetime, smalldatetime and negative datetime
DECLARE @dt AS datetime, @sdt AS smalldatetime
SET @dt = RAND() * 65535
SET @sdt = @dt
SELECT @dt [Original DateTime], dbo.fn_OnlyDate(@dt) [Only Date], dbo.fn_OnlyTime(@dt) [Only Time]
SELECT @sdt [Original SmallDateTime], dbo.fn_OnlySmallDate(@sdt) [Only Date], dbo.fn_OnlySmallTime(@sdt) [Only Time]
SET @dt = -RAND() * 53650
SELECT @dt [Original DateTime (Negative)], dbo.fn_OnlyDate(@dt) [Only Date], dbo.fn_OnlyTime(@dt) [Only Time]

-- Check integrity
DECLARE @i int, @errors int
SET @i = 0
SET @errors = 0
WHILE (@i < 1000000)
BEGIN
SET @dt = RAND() * 65535
SET @sdt = @dt
IF (@dt <> ( dbo.fn_OnlyDate(@dt) + dbo.fn_OnlyTime(@dt) ))
SET @errors = @errors + 1
IF (@sdt <> ( dbo.fn_OnlySmallDate(@sdt) + dbo.fn_OnlySmallTime(@sdt) ))
SET @errors = @errors + 1
SET @i = @i + 1
END
PRINT @errors

Easy solution ...

CONVERT(DATETIME, CONVERT(VARCHAR, getdate(), 111)))

Also ...

1. Date & Time truncated to date ...

CAST(CONVERT(VARCHAR, getdate(), 111) AS DATETIME)

'2008-07-18 14:15:29.000' -> '2008-07-18 00:00:00.000'

2. Date & Time truncated to minutes ...

CAST(LEFT(CONVERT(VARCHAR, getdate(), 120), 16) AS DATETIME)

'2008-07-18 14:15:29.000' -> '2008-07-18 14:15:00.000'

Great stuff; please subscribe me.

Thanks.

INSERT INTO #date (Date) values (GetDate())
go
SELECT Convert (char (10), GETDATE() , 101)
FROM #date

I just used select convert( varchar(10), getdate(), 101)

Isn't it that easy? maybe I missed something

>> I just used select convert( varchar(10), getdate(), 101)

>> Isn't it that easy? maybe I missed something

That works fine for display, but if you want to store the data and be able to do data operations on it, I think you need to convert it back to datetime...

René

Beautiful! Thank You!

Just a quick usage point...

When you want to compare two dates minus their times, the halves of the above expressions that convert from int to datetime aren't necessary, so all you need is

datediff(d,0,first) < datediff(d,0,second)

Lovely! It's so compact I think I might actually remember it...

I tried this once and it's working ..

DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

though I dunno about the performance compared to the two proposed methods ..

And this is why I prefer Oracle. trunc() gives you back the date only, and keeps it in date format for date math like last_day().

Can't believe you have to jump through hoops this much in SQL Server.

This has been long thread and I enjoyed reading it completed. It has been long time since I commented on my friend Ben's blog. I have been busy with so many things.

This is really interesting that Ben goes in depth of understanding the subject matter.

A year ago, I had done similar performance comparison of two of SQL Server Date Part methods which are discussed above. I am sure readers will like it too.

http://blog.sqlauthority.com/2008/10/18/sql-server-retrieve-select-only-date-part-from-datetime-best-practice-part-2/

http://blog.sqlauthority.com/2007/06/10/sql-server-retrieve-select-only-date-part-from-datetime-best-practice/

Let me know what everything thinks.

Regards,
Pinal Dave

How do I write a query which display ONLY the time in this format (14:28:10) if it
is stored as 12/5/2009 14:29:10 in the database?

Hi TO All

I am trying get the string from middle position.
Just try it i hope it will work.

select substring(column_name, start index, last index);

Thanks .

I'd recommend avoid strings entirely.

select month(cast(convert(varchar,getdate(),105) AS datetime)), month(cast(convert(varchar,getdate(),101) AS datetime))

In my UK locale this returns 9 and 6 for the month values -- i.e. the month() function returns an incorrect value because the code converted the date to arbitrary locale-specific string formats in the intermediate steps.

Someone above said "avoid strings, they're slow".
I'd add to that: "avoid strings, they'll corrupt your calculations".

The above calculation works if we cast to smalldatetime instead of varchar because we keep everything in unambiguous binary formats.

However my preferred approach is the one by MatB which avoids casting and converting:

select getDate() AS DateAndTime, DATEADD(DAY,DATEDIFF(DAY, 0, getDate()),0) AS DateOnly

Dan's original FLOOR approach is good too. If you need indexing it looks like Joseph knows what he's talking about!

Great blog. Some really great contributions. Thanks everybody.

I developed the solution for date only from 'getdate()' function. Also this worked for me very effectively. Because I does not required to change the data type in the database or not required to write down different function for each time or not required to execute the same function for so many times.
I use this method.
1) Keep data type of column as 'datetime'.
2)Keep its default value to 'getdate()'.
3) Write trigger on that table as

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[TABLE NAME]
FOR INSERT, UPDATE, DELETE
AS
UPDATE [TABLE NAME] SET [COLUMN NAME] = CONVERT(datetime,CONVERT(varchar(20),[COLUMN NAME], 111), 111)

It works very effectively.

I am not creating separate column. My trigger is on insert, so when I insert a record, first it will insert value of getdate(), and in trigger value get modified in required format.

Sandip
www.aikadajiba.blogspot.com

Recently, I asked a question about this on StackOverflow. Let me know if you like the method they recommend: http://stackoverflow.com/questions/467103/ms-sql-date-only-without-time

here is another option - should be faster...

ALTER FUNCTION [dbo].[GetDateOnly]
(
@DateToFix DateTime
)
RETURNS DateTime
AS
BEGIN
DECLARE @dateMask bigint
DECLARE @timeMask bigint
SET @dateMask = 0xffffffffff000000
SET @timeMask = 0x0000000000ffffff
Return CAST(CAST((CAST(@DateToFix as binary) & @datemask) as binary(8)) as datetime)
END

Thank you, you've helped me with this problem,
as for my database, (Mysql) i can do a cast to Date, wich strips the time part.

Great !! Work for me Thanks Adalberto

The "cast floor" method for Date and time has been a great help. I have always used the Convert funtion in the past.

30 seconds turning into 4 seconds is HUGE!

Thanks,

P.S. I tried the DateAdd solution also, I did not receive any preformance gain with it. Maybe I did something wrong?

@Jeremy - Your P.S. is ambiguous. Performance gain vs. which method(s)? I've seen zero methods perform better than the datediff/dateadd scenarios in my own tests.

This does it, it's easy no sweat no getting fancy no craziness this is it.

select Convert(datetime, Convert(VARCHAR, getdate(), 101) , 101)

This is the fastest most efficient way without doing any converts/casts:

DATEADD(dd, DATEDIFF(dd,0,getdate()),0)

The DATEDIFF is fast Because it doesn't have to do conversions (implicit or explicit) on the target data field.

It is also implementation independant.

And it's SARGable. It doesn't bust the optimiser's ability to utilize indexes.

From both performance and engineering perspectives it's the way to do when dealing with DATETIMEs.

Oh, and it's a good inteview question too.

Anyone, and I really mean this, who proposes string manipulation to solve mathmatical issues should never work for your company.

People don't understand the overhead of string manipulation don't understand enough to Ever be able to predict the consequences of design decisions. They may know the syntax, but they have no real idea of what is going on 'under the hood' and that's a truely dangerous person to employ.

IMHO :)

@Ben,

The post of September 21, 2007 at
Getting Only The Date Part Of A Date/Time Stamp in SQL Server (Revisited) ("www.bennadel.com/blog/963-Getting-Only-The-Date-Part-Of-A-Date-Time-Stamp-in-SQL-Server-Revisited-.htm"), states findings that the DATEADD/DATEDIFF method was approximately equivalent in performance to the CAST/FLOOR/CAST method, no?

...but that the CONVERT/CONVERT or CAST/CONVERT methods were significantly faster?

There are many tests that I have seen run to compare the two options. Unfortunately I've also seen very few that I consider reliable.

Some use Stored Procedures, some use inline queries. Some base it on time, some on reads, some on relative query load when looking at execution plans.

Some are flawed due to caching, using a live server with varying load. Even a test server only you are on has operating system processes running in the background.

My own research shows (both experimental and asking "DrGoogle") shows DATEADD/DATEDIFF to be the most performant in real queries.

If I see a CAST/CONVERT version in code, I leave it though. Much of this is down to preference and the benefit of 'correcting' the code is often not worth the human consequences (people being people get offended)

If I see string manipulation to so this, well I find the biggest heaviest object I can lift and drop it on someone's head.

@Mat,

>> ...DATEDIFF... is also implementation independant. And it's SARGable. It doesn't bust the optimiser's ability to utilize indexes. From both performance and engineering perspectives it's the way to do when dealing with DATETIMEs.

>> ...who proposes string manipulation to solve mathmatical issues should never work for your company. People don't understand the overhead of string manipulation don't understand enough to Ever be able to predict the consequences of design decisions. They may know the syntax, but they have no real idea of what is going on 'under the hood' and that's a truely dangerous person to employ.

Based on initial findings that I referenced above, which seem to be many people's experience regarding the performance of the CONVERT methods, I'm not sure whether CONVERT really not be the faster method.

However, I have no personal experience nor rigorous investigation of other people's experience.

Further, I tend to agree with you regarding a recommended approach for the other reasons that you mentioned.

@Ben, I believe it is the fastest way period in pre 2008 sql.

Thanks @Mat for giving a through explaination.

One important factor seems to be left out here ... namely readability of the code. In most cases it doesn't matter one way or the other whether a query runs in 20 ms or 40 ms so performance isn't always an issue. When performance matters, then you can sacrifice readability for speed.

It is important to realise that others may end up maintaining your code, and in such cases performance doesn't matter so much if that person struggles to understand your code.

K.I.S.S. - Keep It Simple Stupid

I agree with KISS. But part of that, imho, is to avoid manipulations on undefined behaviour.

Although a binary manipulation of a float representation of DateTime does work, it's not a guaranteed behaviour.

Conversley using DATEADD and DATEDIFF are highly explicit and should be easy to understand.

(Add x days to '0', where x is the number of whole days between '0' and the specified date)

I've never found a criteria where DATEADD/DATEDIFF fails:
- Pure Performance
- Real world performance
- SARGable
- Readable
- Explicitly Defined behaviour

Using string manipulation functions is fine if the pupose is to format the date (as text) for output to a report, or displaying query results (without timestamp). For data manipulation I agree it is best stick to mathematical functions, something computers do best. I like DATEADD/DATEDIFF, so I'll be swapping to that method in future...

Anyone know of an alternative method to string manipulation for removing the time portion from datetime field (without displaying '00:00:00.000' for the time portion)? Is this even possible?

I wouldn't even tend to use SQL for that either. I'd use the client. Pass the data to the client as a DateTime and then let the client format it however it wants.

A principle I try to adhere to is that the SQL Server code should not be concerned by display issues. By moving that to a different layer you reduce how tightly coupled the code is, a much more flexible situation. I've seen people tie themselves in knots when reusing a stored procedure, but needing slight tweaks for GUI purposes, yet not wanting to break the original use.

@Mat,

>> A principle I try to adhere to is that the SQL Server code should not be concerned by display issues. By moving that to a different layer you reduce how tightly coupled the code is, a much more flexible situation.

This is a crucial issue that too often is still ignored.

What if you are returning a sql query resultset as an email attachment from sql server using sendmail stored procedure ... is there another option for formatting?

Apologies if someone mentioned it, I'm not reading all the comments lol! But do you even need the floor?

Select CAST(cast( GETDATE() AS INT ) AS DATETIME)

Converting to INT doesn't work in all cases as it will round up to the following day for timestamps after 12:00 noon.

SQL Server 2008's new datatype of date lets us get just the date part of a datetime easier without the trailing zeros.

Select convert(date, GETDATE(), 103) as EVENT_Date

I can't remember the last time I found a thread that was so helpful and so detailed. Thank you.

SQL 2008 introduced the data type DATE without the time. So in 2008 it's as easy as CAST([DateTimeColumn] AS DATE)

thanks for your thread but i have got a question i am not sure if i am doing it correct. I have got a view and select columns as:

SELECT dbo.SIRKET.ADI AS SIRKET_ADI, dbo.KART_SAHIBI.ADI, dbo.KART_SAHIBI.SOYADI, dbo.GECIS.KARTSAHIBI_ID, dbo.GECIS.GIRIS_KAPI_ID,
CAST(dbo.GECIS.GIRIS_ZAMANI AS DATE) AS GIRIS_TARIHI, CAST(dbo.GECIS.GIRIS_ZAMANI AS TIME) AS GIRIS_SAATI.......

when I execute the query i have the following error message:

cannot call methods on date.

Any idea_?

I used the CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) method. The architect of my company said it is too complex. He wanted to use CONVERT(varchar, GETDATE(), 101) so that every people will understand.

Thanks!!...this helped a lot. The float method is really good. It is better than any other logic I had found so far. Most important is that it is convincing logically.

Nic-
Is there some reason you can't just convert the datetime field to a text field that only shows the date? If it's an email attachment from a straight SQL query the user can just copy/paste it into a spreadsheet and it should register as a date value.
-TB

TB-
Converting to a text field that only shows the date is fine, and valid, even if it isn't the most efficient way to remove a timestamp.
-NB

Anyone that converts any data type (DateTime or otherwise) to a string for Manipulation is likely never to work on any team with me.

Without very specific case by case reasoning, it should only be done if the String is the desired end result (such as for an email). Definately not if you're going to convert it back to a DateTime afterwards.

It basically shows either a lack of knowledge, or complete disregard, for what is actually happening 'under the hood'.

Mat-
My understanding is that the desired end product was a straight text file. In this instance, datatype become irrelevant. In general I wouldn't advocate for switching a datetime field to any other format unless expressly required, but for this specific instance I believe my comment still stands (you'll notice Nic agreed).
-TB

On MS SQL 2005 it works:
SELECT CAST(CONVERT(varchar, GETDATE(), 112) AS DATETIME)

i think a few people were close but made a slight mistake using the datediff methods.

I use a DATEADD and DATEDIFF a lot and it's always worked perfectly for me

DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

Hope that helps!

Thanks very much for all the useful information.

The function below shows 12(!) different techniques (including a few inspired by this thread), which I've proved return identical results for a spread of times on every possible date. Just comment out one line at a time to try each technique. It should be noted that all 12 techniques are deterministic.

As far as performance is concerned, you have to apply the function to over a million records before there is much appreciable difference.

My conclusion is that techniques 1, 3, 4, 8 and 9 are the fastest, although I'd be very interested to hear if anybody else could identify a clear winner.

I agree with the general conclusion of this thread that technique 1 is the overall winner for its combination of readability, future proofing and performance.

CREATE FUNCTION f_DateOnlyDeterministic
(
@DateTime DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
RETURN DATEADD(DAY, 0, DATEDIFF(DAY, 0, @DateTime)) --technique 1
-- RETURN CONVERT(DATETIME, CONVERT(VARCHAR(8), @DateTime, 112), 112) --technique 2
-- RETURN CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @DateTime, 112)), 112) --technique 3
-- RETURN CONVERT(DATETIME, CONVERT(INT, @DateTime, 112) + CASE WHEN DATEPART(HOUR, @DateTime) >= 12 THEN -1 ELSE 0 END, 112) --technique 4
-- RETURN CONVERT(DATETIME, RIGHT('000' + CONVERT(VARCHAR(4), YEAR(@DateTime)), 4) + RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@DateTime)), 2) + RIGHT('0' + CONVERT(VARCHAR(2), DAY(@DateTime)), 2), 112) --technique 5
-- RETURN CONVERT(DATETIME, RIGHT(10000 + DATEPART(YEAR, @DateTime), 4) + RIGHT(100 + DATEPART(MONTH, @DateTime), 2) + RIGHT(100 + DATEPART(DAY, @DateTime), 2), 112) --technique 6
-- RETURN CONVERT(DATETIME, CONVERT(VARCHAR(2), DAY(@DateTime)) + '/' + CONVERT(VARCHAR(2), MONTH(@DateTime)) + '/' + CONVERT(VARCHAR(4), YEAR(@DateTime)), 103) --technique 7
-- RETURN CONVERT(DATETIME, CONVERT(INT, SUBSTRING(CONVERT(BINARY(8), @DateTime, 112), 1, 4)), 112) --technique 8
-- RETURN CONVERT(DATETIME, CONVERT(INT, CONVERT(BINARY(4), CONVERT(BINARY(8), @DateTime, 112))), 112) --technique 9
-- RETURN CONVERT(DATETIME, SUBSTRING(CONVERT(VARBINARY(8), @DateTime, 112), 1, 4) + 0x00000000, 112) --technique 10
-- RETURN CONVERT(DATETIME, CONVERT(VARBINARY(8), CONVERT(VARBINARY(8), @DateTime, 112) & CONVERT(BIGINT, 0xffffffff00000000)), 112) --technique 11
-- RETURN @DateTime - CONVERT(BINARY(4), @DateTime, 112) --technique 12
END

Actually Brian Young's post directly before mine is technically correct.

He suggests using:

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

instead of using:

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

which is a closer representation to what we're trying to achieve. About half the DATEADD examples in this thread (including mine) have them the wrong way round.

However, they both give identical results. Taking the date of this post as an example (31 January 2010) then his suggestion adds 40207 to 1 January 1900, rather than adding 0 days on to 31 January 2010 (which is what we get when the 40207 result of the DATEDIFF is implicitly CONVERTed to a DATETIME value.

However, this showed me that the DATEADD in my erroneous example was just converting the INT return value from DATEDIFF into a DATETIME. So, a 13th technique is:

RETURN CONVERT(DATETIME, DATEDIFF(DAY, 0, @DateTime), 112)

However, I've also just noticed that leaving out the CONVERT and allowing an implicit conversion back to the return type of my function also works and leaves the function deterministic. That is:

RETURN DATEDIFF(DAY, 0, @DateTime)

This only works because of the implicit conversion so be careful about using this technique. The CONVERT version is explicit and can be used directly in a SQL statement.

Finally, for those of you who don't like the use of the 0, you could use:

RETURN DATEDIFF(DAY, CONVERT(DATETIME, 0, 112), @DateTime)

or

RETURN DATEDIFF(DAY, CONVERT(DATETIME, '19000101', 112), @DateTime)

These techniques still leaving the function deterministic too.

Thank you Ben!
You so help me.

Dmitry - I was always told it was bad practice to mix cast and convert.. not sure what people think.

I've found that for 10k to 100k rows plus that the following is most performant.

CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 113))

I think that the solution is on the presentation, i saw that many people use it to display the info with an app with .net well the answer is on the mask.. if you are using a gridview to display the date just add the parameter DataFormatString="{0:dd/MM/yyyy}" in the date column; now if you want to do so, in sqlserver well there is no solution if at the end you convert your float value again to date 'cause it will display the hour (00:00) so i think the best approach would be to cast it as varchar and then make a substring like this... subtring(cast(getdate() as varchar),0,12)

cheers!!

For anyone attempting to get the end of the day as well (11:59 PM), just add a fraction to it. Make sure it has 8 nines to get to the max millisecond before midnight.

print cast(0.99999999 + floor(cast( getdate() as float )) as datetime)

I mainly needed it to use for "date between @start and @end"

Interesting dicusions above. I too have longed for a better way to truncate dates like Oracle does. I have put together the following function that I believe combines some of the best suggestions from haylo75, joseph and matB (forgive me if I've not credited anyone else). The system says this function is deterministic. I do not know what the performance statistics of this are compared to other single use functions that only do days, hours, etc.

-- =============================================
-- Author: TBonzai
-- Create date: 05/03/2010
-- Description: Returns a date truncated to a specific unit of measure.
-- Works like the Oracle function of the smae name, except it uses MSSQL datepart names.
-- Parameters: @datein [required] The original date.
-- @datepart [optional] The datepart to use as a measure. Blank or non-recognized values return the original date.
-- =============================================
CREATE FUNCTION [dbo].[ufn_Trunc]
(
@datein DATETIME,
@datepart VARCHAR(4)
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @returnDate DATETIME;
SET @returnDate = @datein;
SELECT
@returnDate = CASE @datePart
WHEN 'ms' THEN DATEADD(ms, DATEDIFF(ms, 0, @dateIn), 0) -- millisecond
WHEN 's' THEN DATEADD(ss, DATEDIFF(ss, 0, @dateIn), 0) -- second
WHEN 'ss' THEN DATEADD(ss, DATEDIFF(ss, 0, @dateIn), 0) -- second
WHEN 'n' THEN DATEADD(mi, DATEDIFF(mi, 0, @dateIn), 0) -- minute
WHEN 'mi' THEN DATEADD(mi, DATEDIFF(mi, 0, @dateIn), 0) -- minute
WHEN 'hh' THEN DATEADD(hh, DATEDIFF(hh, 0, @dateIn), 0) -- hour
WHEN 'd' THEN DATEADD(dd, DATEDIFF(dd, 0, @dateIn), 0) -- day
WHEN 'dd' THEN DATEADD(dd, DATEDIFF(dd, 0, @dateIn), 0) -- day
WHEN 'wk' THEN DATEADD(wk, DATEDIFF(wk, 0, @dateIn), 0) -- week
WHEN 'ww' THEN DATEADD(wk, DATEDIFF(wk, 0, @dateIn), 0) -- week
WHEN 'm' THEN DATEADD(mm, DATEDIFF(mm, 0, @dateIn), 0) -- month
WHEN 'mm' THEN DATEADD(mm, DATEDIFF(mm, 0, @dateIn), 0) -- month
WHEN 'q' THEN DATEADD(q, DATEDIFF(q, 0, @dateIn), 0) -- quarter
WHEN 'qq' THEN DATEADD(q, DATEDIFF(q, 0, @dateIn), 0) -- quarter
WHEN 'yy' THEN DATEADD(yyyy, DATEDIFF(yyyy, 0, @dateIn), 0) -- year
WHEN 'yyyy' THEN DATEADD(yyyy, DATEDIFF(yyyy, 0, @dateIn), 0) -- year
ELSE @datein
END;
RETURN @returnDate;
END

Oops.. forgot to remove one unnecessary assignment line. You do not need the line.

SET @returnDate = @datein;

In a table with thousands of records,one column being datetime, how can I divide the day in to 2 sections? Lets say 2 shifts. 1st shift starting at 04:00 and ending at 15:30, second shift starting at 15:30 and ending at 04:00. anyone has a suggestion for this? Thank you all.

@TBonzai,

Thank you, it work but I also need the date to be variable, I am currently using two querys with different times.:

where my_datetime_column between DATEADD(hour, 4, DATEDIFF(DAY, 0, current_TIMESTAMP)) and DATEADD(minute, 870, DATEDIFF(DAY, 0, current_TIMESTAMP))

Aaargh, from time to time I have trouble posting long comments on here. A few days ago, I posted a long reply to TBonzai's posting of a UDF, linking to many external resources and articles to explain why UDFs will always tank performance, especially when used on large tables. In the hopes that this reply will go through, I will link only to my own testing script, which is heavily noted, and will hopefully explain to all why I personally avoid UDFs when aggregating data.

In short - after years of testing and experimentation, when truncating dates in SQL Server I always use the DATEADD/DATEDIFF methodology that TBonzai nicely encapsulates in his UDF. I, do not, however, do so using UDFs, due to performance concerns which are explained in much more detail in the linked script.

Hopefully this comment will post. :/

http://www.gtvault.com/scripts/sql_server_date_truncation_tests.sql

Hello Kerr,

That is odd that you post of May 4 doesn't appear, either on this page, on in the collection of all of your posts...

I did receive an email of your post, and it was very helpful. If you need it, I could send it to you. Perhaps Ben can figure out why it disappears.

Regards,

René

Hmm, I just noticed the "Comment Etiquette" notice, not to post "large chunks of code".

René - Whoops, I bet that's why my post ended up not showing up here in the thread. ;) The link to my test script is the most important thing, and it looks like that was retained in my post from today.

hmmm... why not:

SELECT Convert(DateTime, Convert(INT, GETDATE()))

Converting to an INT, before converting to datetime will round the value not truncate the time portion, so you can end up with the incorrect date, depending on whether the time was before or after 12:00 mid-day.

All, I'm a newbie and I'm trying to move a date function from MySQL to SQL Svr 2008. In MySQL it looks like:

begin
RETURN cast(concat(year(thisDate),right(concat('00', month(thisDate)),2)) as unsigned int);
end

Return Type: int(11)

The end result is to convert a stored full length time stamp to YYYYMM as part of the output in a view.

I need a lill help from you all smartys!

I am working on an update of a datetime field and receiving the following error.

Query:

Update tbl
SET Month(column 1) = Month(Column 2),
Day(column 1) = Day(column 2)
WHERE Month(column 1) = Day(column 2)
and Day(column 1) = Month(column 2)
and id in (111,222,333)

Error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

Please assist.

Thanks, - imransi -

You need single quotes around the values - where id in ('','','')--

Update tbl
SET Month(column 1) = Month(Column 2),
Day(column 1) = Day(column 2)
WHERE Month(column 1) = Day(column 2)
and Day(column 1) = Month(column 2)
and id in ('111','222','333')

No, single quotes will not work. the error is from line 1. following is what i am trying to do.

Example:
Column1 Column2
2009-01-08 19:42:00.000 2009-08-01 19:42:00.000

Also, I don't know the code to update just the month and also just the day with in a datetime column.
Please assist !

I think this is the "standard" way of getting a date without time from sql...

SELECT CONVERT(VARCHAR(10),'6/16/2010 3:59:59',101)

Hi. just wanted to say THANKS for a simple, easy to follow article. I have SQL Server 2005 for development but our production is still SQL 2000. I had to check dates of holidays and not worry about the time. This worked like a charm for me!

In reply to R A Valencourt on deterministic functions.
My colleagues and I found that as stated in the manual

----------------------------------------------
CONVERT
Deterministic unless one of these conditions exists:

Source type is sql_variant.

Target type is sql_variant and its source type is nondeterministic.

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.
-----------------------------------------------

Checked this out and Convert is determistic provided you use 100+ styles not including exceptions above. I would guess that the other functions are non-deterministic because rollover years for 2 digit years can be set to different values.

So the simple chararacter conversion using CONVERT may not be the fastest but it can be deterministic and henced used in indexes.

Geoff

Geoff

As I said in my posts, all 16 techniques that I describe are deterministic. They cover a variety of ways of solving the problem, not just character conversion.

Russ

Russ,

My explanation was in relation to a person stating that sometimes they found CONVERT was deterministic and sometimes not. All your examples are determistic but the reason why is not stated.

Thanks for your posts I did find them useful.

Kind Regards,

Geoff

Hey Geoff

I'm glad you found my posts useful.

Perhaps I misinterpreted your sentence "So the simple chararacter conversion using CONVERT may not be the fastest but it can be deterministic and henced used in indexes."?

I thought you meant that it was worth using the slower technique because of its determinism. However, since the faster techniques can also be made deterministic then I felt I should clarify.

Sorry for any misunderstanding.

Russ

@Geoff,

On Apr 15, 2008 I wrote in part:

>> It just makes a blanket a priori determination that if you use the cast/convert operation on a date / float, that the formula will not be deterministic...

You wrote:

>> Checked this out and Convert is determistic provided you use 100+ styles not including exceptions...

I had been addressing the use of cast and floor; I'm not sure at this point over two years later why I mentioned the convert operation, since I made no prior or further mention of it, except that it is Microsoft's version of cast... Perhaps I had performed some sort of test, or perhaps I made an unfortunate assumption?

Anyway, thanks for your contribution. I will try to find time to see whether I can replace cast with convert in my function and build an index with it...

Thanks!

Rene

thanks everything, it helps thank you

convert(char(10),getdate(),101)

@Ben,
Your solution looks more elegant than what I've been using in the past, but wouldn't the following be just as efficient or is floor() better than cast to varchar?

cast(cast(getdate() as varchar(11)) as datetime)

very nice suggestion, it helped me a lot!

thank you!!

Wed-04-Aug-2010 9:10:11 AM in string form in vb
want to convert into datetime data type , i want to remove day 'wed' in sql from string Wed-04-Aug-2010 9:10:11 AM

@keth,

One option is to split it as follows.
<%
response.write(MonthName(month(date()))&" "&day(date())&","&year(date()))
%>

I just wanted to thank the original poster (Ben I think?) This worked perfectly for me in a matter of minutes. Very much appreciated!!!!

How i can add minutes using date add function in stored procedure . If any alternative pls tell that.
Set @Apoint_AproxStime =dateadd(hh,2,@Apoint_AproxStime)

i passed @Apoint_AproxStime = '09:10 AM'
and executed procedure it gives a invalid result

@Purushothaman,

DECLARE @mydatetime DATETIME

SET @mydatetime = '09:10'
SET @mydatetime = DATEADD(HOUR, 2, @mydatetime)

SELECT @mydatetime

Thank you! This post saved the day for me. I've been using all kinds of hacks to simulate this, but this is exactly what I needed.

At first I was getting errors, but then realized I was missing a comma. Those commas are very important. :-)

Thanks again!

I'm doing a GROUP BY on TSQL, so the solution CAST(FLOOR) was the best one. Thank you Ben.

This is not the easiest syntax.

See below where the two expressions accomplsih the same thing; ie. to get the item where the depublicationdate is seven day after the current date.

The second expression is much easier to read:

SELECT memorialcollectionid
FROM dbo.tbl_MemorialCollection
WHERE
	CAST(FLOOR(
	CAST(memorialcollectiondepublicationdate AS FLOAT)
	)AS DATETIME)
= CAST(FLOOR(
	CAST(GETDATE()+7 AS FLOAT)
	)AS DATETIME)
 
 
SELECT memorialcollectionid
FROM dbo.tbl_MemorialCollection
WHERE 0 = (SELECT(DATEDIFF(day, GETDATE()+7, memorialcollectiondepublicationdate)))

DATEADD(d, 0, DATEDIFF(d, 0, @MyDate))
-------------------------------------
^^^^
works perfectly. Tested SQL2008.

i have query of sql 2005

Select Truck_No,Report_No, Convert(Varchar,Rep_Date,05) As "RDate"
From RecTruck
Where Rep_Date Between Convert(Varchar,'2010-11-01',05) And
Convert (Varchar,'2010-11-30',05) And Trk_Frm='AHMEDABAD'

Result of 20Rows

AP13X4130 163 01-11-10
AP13X1370 164 02-11-10
AP13X4473 165 03-11-10
MH25U7194 166 03-11-10
GJ23V2577 167 04-11-10
MH25B7586 168 10-11-10
AP13T4297 169 12-11-10
AP13X5739 170 13-11-10
AP12U8302 171 13-11-10
KA32A8191 172 16-11-10
KA394634 173 16-11-10
AP13X4035 174 18-11-10
AP12U9438 175 20-11-10
AP13X6851 176 19-11-10
GJ1BV1891 177 19-11-10
GJ11W4972 179 23-11-10
KA394634 180 24-11-10
GJ23V2577 181 26-11-10
AP12U8128 182 26-11-10
AP25V9609 183 27-11-10

Now The question is my table has a value of 21 row
on Date 30-11-10 but the above said query is not
displaying that row ?

Please help me to solve out this

THX in advance

Hi IP,

If [Rep_Date] has a 'time' component (other than midnight) with the date, then it will be outside the range of your 'BETWEEN' clause ... its simple to fix, so I'll leave that with you.

Can't believe Microsoft didn't think about having "date" in a seperate field for calculation and other purposes ...

Thanks, the INT works for me!
I am using Clarion and it handles the date function quite nicely with the value from INT.

I have for years created two columns for all dates (very easy to index). If I have a column named ModifiedDateTime, I will always add ModifiedDate both as dateTime-datatype. Ad DueDate/DueDateTime etc.

Then in whatevery frontend, I will create the ModifiedDate as, in this case CF without the time:

<cfset lInsertDate = CreateODBCDate(CreateDate(DateFormat(Now(),"yyyy"),DateFormat(Now(),"mm"),DateFormat(Now(),"dd")))>

This way, I can easily do group-by queries by ModifiedDate and not worry about the time-part of the date. On tables where the date-only column is missing I will create it and run a CreateODBCDate on the date, then index this new column. Works every time, is really fast, and makes for clear code for T-SQL and GROUP BY.

The INSERT will look like:

<cfquery>
UDPATE sometable
SET somecol = somevalue,
 
ModifiedDate = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#lInsertDate#">,
ModfiedDateTime = getdate()
 
WHERE someID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Val(variables.somevalue)#">
</cfquery>

If you want to have the serverTime matching the application server or getdate, as they could be different, create another date lInsertDateTime from the app-server. If you need the server time of the sql-server, run a getdate() query for the server time and use it to create the odbc-date/time values.

@Eric,

Are you working with SQL Server pre-2008? If you're on 2008, you have access to the dedicated DATE and TIME data types. They save a lot of storage space and still provide the separation you seek. We're in a (mostly) 2005 environment, so I don't have access to them.

@Ben - OT, but I've been long pining for a Preview Comment button on your site. :)

Simplest of all:

SELECT
REPLACE(CONVERT(nvarchar(20), GETDATE(), 111), '/', '-')
AS JustTheDate -- yyyy-mm-dd

I've been searching the web for a while now and I can't find an answer... if i only need date from a datetime column with sql server 2008 is CAST(thedatetime AS DATE) or DATEADD(dd, DATEDIFF(dd,0,thedatetime), 0) more efficient?

It seems like there's still a lot of old dogs swearing by dateadd/datediff based on testing from years ago and not against the newer 2008 cast.

Hi Ben,

Great blog, use it a lot. I am running SQL Server 2008R2 and I just go:

CAST(GetDate() AS DATE)

AND

CAST(GetDate() as TIME)

Am I missing an obvious benefit by Flooring the Float first?

Or do you think the above will work just fine everywhere plus performance wise?

Regards,
Dewald

@Dewald, the need for converting to a floored float and then back to datetime is that for previous versions of SQL Server the DATE and TIME data types do not exist.

On 2008R2 those methods are by far the most straightforward. As far as performance goes, I recommend side by side tests. This thread is epic in length, but somewhere above I linked to some testing scripts I wrote years ago.

good post. Me thinks this will be useful in my next project, being that I will need to do a report for a whole month, and go backwards from the date the person is accessing the report, and go all the way back to the beginning of the last month, through the end of the last month. For some reason, it seems in my mind that there is a future project that will require me to use the time (perhaps and/or the date) part of the stamp, but I should probably be taking this one project at a time. :-/

sir i hv defined datetime in table..
nw i m getting error in my .php page when i try to get date and time from their..

Date : <?php if (ereg ("([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})", $temptwo['datetime'], $regs)) {
echo "$regs[3]/$regs[2]/$regs[1]";}
else {echo "Invalid date format";} ?><BR>
Time :<?php if (ereg ("([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})", $temptwo['datetime'], $regs))
{echo "$regs[4]$regs[5]";} ?>

I like the floor() one personally, but the one that sticks in my brain for some odd reason (mainly cos it just works) is this one:

select convert(datetime,convert(varchar(10),getdate(),120),120) today

You can also change the varchar(10) to the length in the ISO date format 120 to the portion you desire.

I daresay this is somewhat slower than using floor though.

"I daresay this is somewhat slower than using floor though." - That is definitely an understatement. See my post dated May 13, 2010 at 8:49 AM with a link to a test SQL script. The takeaway: Any method which converts data to another data type is going to be very slow. Dates are numbers. Treating them as such will net the best performance.

Great post - worked a treat - thanks heaps

terrific solution !!

atte

fan del memo padres

Why dont we just try this:

CAST(dateTimeStamp AS DATE)

this returns the DATE part or dateTimeStamp
as simple as that

@Dewald,

that works just fine and is not confusing

@Praveen,

That only works fine if you are on SQL Server 2008+, when the DATE data type was introduced. A lot of this discussion occurred before that product release.

Also, that still only gets you to the date itself and will not help in situations where you want to get the month, quarter or year. These are all very common requests in reporting scenarios.

I'd still be willing to bet that casting in and of itself may not provide the best performance as opposed to other non-casting solutions. People don't think about the danger of casting when doing rollup queries on large data sets. I assure you it is far more CPU intensive than one might imagine. Anecdotal, "It's fast enough", research is not enough. Side-by-side testing of the various methods is the only way to get a real answer. If you wish, take a look at a script I linked to some time ago and add your own cast(column as date) test to the mix.

http://www.gtvault.com/scripts/sql_server_date_truncation_tests.sql

Hi,

I have not much idea on MS-SQL.Though i have started to work in this technology.

As you are saying To use CAST FLOOR method to get only date part,now if i want to perform that date part trimming on a column of datetimestamp column say(i.eEXCEPTIONDATETIME)how should i proceed??

Plz help.

thnx in advance

Always used:

cast(datepart(yyyy,getdate())+'-'+datepart(mm,getdate())+datepart(dd,getdate()) as datetime)

@sean,

Not in SQL Server.

also try this for fetch just date..http://codingresolved.com/discussion/62/how-to-get-date-from-datetime-in-sql#Item_2

and also this link for get just date in C#...http://codingresolved.com/discussion/69/how-to-get-only-date-from-string-in-c#Item_2

I like the method because it's simple and elegant, but I want to mention one thing that focused my attention. Look how rich/complex/intrincate/indirect/awkward is the programming science, as one simple thing like this produces soooo many comments + 1 !!!

I use this, works in SQL Server. First you get the date into the string format you want, then cast it date.

cast(convert(varchar(10),getdate(),101) as datetime)

hi..
i want to combine all time stamps of the day in to that day.how can i do that..?
My output is:
Date Quantity
2013-01-01 06:01:34 2
2013-01-01 22:04:45 4
2013-01-05 04:12:23 1
2013-01-05 11:01:32 2

Required output:

Date Quantity
2013-01-01 6
2013-01-05 3

thanks in advance if someone can help me.

Hy!
Why don't you use 'datepart' to do this?
thanks.

Hi Ben,

I know very little about SQL code. My only programming background is what I've taught myself using VBA in Excel, so my methods are more than a little sketchy.

Having said this, I'm always looking for code pieces I can wrap my brain around and discovered a method that works for my needs.

I'm looking for a relative date for transactions that have occurred today. I've found that this does just what I want it to...

This might be a helpful method for your subscribers who need a relative range, but not an exact match.

SELECT Timestamp
FROM MyTable
WHERE Timestamp > GETDATE()-1

Thanks,

Aaron

Hi! what do you think about this?:

http://runakay.blogspot.com/2014/09/making-queries-to-compare-dates-in-sql.html

I use it for comparing dates.