Date Comparison

[allochthonous]

Other than issues with sorting and MAX, are there any potential issues with using something like CONVERT([varchar](10), datetimefield, 101) which yields a mm/dd/yyyy varchar value.

That is, the WHERE > and < statement will still work properly as long as it is being compared to a true datetime value?

The actual syntax would be :

DECLARE @StartDate as date
SET @StartDate = some calculated date

DECLARE @EndDate as date
SET @EndDate = another calculated date


CONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate
...
WHERE MyDate >= @StartDate and MyDate <= @EndDate


I am not so concerned about efficiency as I am accuracy at this time.

 

[gmmastros]

If you are using SQL Server, you cannot use a column alias in the where clause. You'll get a syntax error.


I usually do this...

[tt]
DECLARE @StartDate as [!]datetime[/!]
SET @StartDate = some calculated date

DECLARE @EndDate as [!]datetime[/!]
SET @EndDate = another calculated date [!]+ 1 Day[/!]


CONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate
...
WHERE [!]MyDatetimeColumn[/!] >= @StartDate and [!]MyDatetimeColumn[/!] < @EndDate
[/tt]

For example, if I wanted to run a query for the month of December 2012, my @StartDate would be Dec 1, 2012 and my @EndDate would be Jan 1, 2013. Notice that I changed the condition for @EndDate to Less than instead of less than or equal to.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[flapeyre]

Or, in SQL Server 2008R2 and above, you can use [tt]BETWEEN[/tt] like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2012-01-01'
SET @EndDate = '2012-12-31'

SELECT * FROM mytable WHERE MyDatetimeColumn BETWEEN CONVERT(DATE, (@StartDate)) AND CONVERT(DATE, (@EndDate))

That should give you the month of December, 2012, only.

What I did was convert the dates from DATETIME to DATE (which strips out the time component). That way, you can use [tt]BETWEEN[/tt].

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[gmmastros]

Flapeyre,

Unfortunately, that doesn't work because you are comparing a DateTime column to a couple Date variables. Based on data type precedence, SQL Server will 'convert' the date variables to a datetime variable for comparison purposes.

Ex:

Declare @Temp Table(Id Int, MyDateTimeColumn DateTime)

Insert Into @Temp Values(1, '20121231 23:30')

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2012-01-01'
SET @EndDate = '2012-12-31'

SELECT * 
FROM   @Temp 
WHERE  MyDatetimeColumn BETWEEN CONVERT(DATE, (@StartDate)) AND CONVERT(DATE, (@EndDate))

If you run the code above on SQL2008R2 (like I did), you will not get any syntax errors, but you also won't get the row from the table where the datetime is 2012-12-31 @ 12:30 PM.

Of course, you could convert the column in the where clause like this:

WHERE  [!]Convert(Date, [/!]MyDatetimeColumn[!])[/!] BETWEEN CONVERT(DATE, (@StartDate)) AND CONVERT(DATE, (@EndDate))

But then you run the risk of the query not being sargable.

Just to be clear, sargable queries are queries than CAN make use of an index if a suitable index exists. Basically, sargable queries are better for performance.

If the original column was Date, then the between query would have been perfectly acceptable. Since it's not, it is better (in my opinion) to use the query that I had suggested earlier.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[flapeyre]

Hi, George,

You are right, of course. As a matter of fact, that's what my original code looks like. I accidentally zapped the CONVERT part when I was formatting the statement for this post.

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[allochthonous]

This is all great info, but really what i was most curious about was whether SQL would correctly interpret the varchar (mm/dd/yyyy) MyDate against the true date fields of @StartDate and @EndDate. The actual range I am looking for does not really matter per se.

PK

 

[flapeyre]

Character to date/datetime conversions are implicit, so, the answer is yes.

CAST and CONVERT (Transact-SQL)

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain