Current Date issue in where clause

Question

Post reply

Current Date issue in where clause

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeremy... Ten Centuries Points: 1299 More actions · Answer 1

Thanks Lynn. Is there a good place to find information on this topic? I've seen the below done 500 different ways and usually have trouble deciphering what some of it means. Thanks.

declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date

declare @StartDate datetime,

@EndDate datetime;

-- Assume that the date entered may have time component that needs striping

set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);

set @EndDate = DATEADD(dd, 1, @StartDate);

Jared SSC Guru Points: 61793 More actions · Answer 2

Jeremy... (2/22/2012)

Thanks Lynn. Is there a good place to find information on this topic? I've seen the below done 500 different ways and usually have trouble deciphering what some of it means. Thanks.

declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date

declare @StartDate datetime,

@EndDate datetime;

-- Assume that the date entered may have time component that needs striping

set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0);

set @EndDate = DATEADD(dd, 1, @ReportDate);

In 2008, I prefer this:



DECLARE @ReportDate DATE --if coming as input parameter, why even have @startDate?

DECLARE @EndDate DATE

SET @EndDate = DATEADD(dd,1,@StartDate)

This avoids the datediff/dateadd crap that we had to use in 2005. If it is simply a date without time, Use the DATE data type. Also, to strip the time off simply do this:

CAST(myDateTime AS DATE)

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

MysteryJimbo (2/22/2012)

Lynn Pettis (2/22/2012)

Jeremy... (2/22/2012)

MysteryJimbo (2/22/2012)

I dont mean to be pedantic but for accuracy I'd want to see this query as a between and in a stored procedure with default values so it can be reused for any day and cater for incorrect data (just in case)

What is a between?

It is in a stored procedure and a report has been created of it and put on the report server.

When you say using default values, do you mean something like:

DECLARE @today datetime

SET @today = CONVERT(DATE,FA.receiveddate)=CONVERT(DATE,GETDATE())

would that be correct? Then substitute @today in the where clause?

I would not use between if I were to write this to return data for a specific date. I would use the follow snippet as a start:

declare @ReportDate datetime; -- Think of this as the input parameter requesting a specified date declare @StartDate datetime, @EndDate datetime; -- Assume that the date entered may have time component that needs striping set @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @ReportDate), 0); set @EndDate = DATEADD(dd, 1, @StartDate); SELECT --- columns to be returned here FROM --- table a (or tables) WHERE a.OrderedDate >= @StartDate and a.OrderedDate < @EndDate -- followed by other sfilter conditions if needed ;

You "could" do it either way; since BETWEEN is inclusive you would have to use a datetime with .997 ms. So 23:59:59.997.

And then you have to change this if you are using datetime2 in SQL Server 2008.

I much prefer using the >= and < when having to select records for a single date, you don't have to worry about the time portion.

Wildcat SSCarpal Tunnel Points: 4714 More actions · Answer 4

Wildcat

SSCarpal Tunnel

Points: 4714

February 22, 2012 at 2:25 pm

#1450562

Hakuna Matata (2/22/2012)

Try this :

WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

I don't recommend to use this

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Lynn Pettis

SSC Guru

Points: 442447

February 22, 2012 at 2:39 pm

#1450570

Wildcat (2/22/2012)

Hakuna Matata (2/22/2012)

Try this :

WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

I don't recommend to use this

I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

Jeremy... Ten Centuries Points: 1299 More actions · Answer 6

Lynn Pettis (2/22/2012)

Wildcat (2/22/2012)

Hakuna Matata (2/22/2012)

Try this :

WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

I don't recommend to use this

I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

so it works, just chews up more processes and takes the query longer to complete?

Jared SSC Guru Points: 61793 More actions · Answer 7

Lynn Pettis (2/22/2012)

Wildcat (2/22/2012)

Hakuna Matata (2/22/2012)

Try this :

WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

I don't recommend to use this

I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

I still prefer CAST over CONVERT for performance:

SELECT DATEADD(ss,RAND(),GETDATE()) AS RandomDate



INTO #TEMP

FROM sys.columns s1

CROSS JOIN sys.columns s2

SET STATISTICS TIME ON

SELECT CONVERT(DATE, RandomDate) AS something

FROM #TEMP

SELECT CAST(RandomDate AS DATE) AS something

FROM #temp

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 8

Jeremy... (2/22/2012)

Lynn Pettis (2/22/2012)

Wildcat (2/22/2012)

Hakuna Matata (2/22/2012)

Try this :

WHERE CONVERT(DATE,FM.Openeddate)=CONVERT(DATE,GETDATE())

I don't recommend to use this

I agree with wildcat as it would cause a table scan. The convert function has to be applied to every row in the table to complete the comparison.

so it works, just chews up more processes and takes the query longer to complete?

It gives you a bad execution plan. Much better to calculate a parameter that will be fixed, and compare column values to that.

Jared
CE - Microsoft