SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date Comparisons
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulkem
Starting Member

25 Posts

Posted - 01/07/2013 :  12:24:50  Show Profile  Reply with Quote
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.

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/07/2013 :  13:13:53  Show Profile  Reply with Quote
Is there a reason you are converting a date to a string so that you can then compare it to a date? Why not leave MyDateTimeColumn as it is?

Jim

P.S. You can't use MyDate in the WHERE clause if MyDate is determined
in the SELECT clause

Everyday I learn something that somebody else already knew

Edited by - jimf on 01/07/2013 13:15:21
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 01/07/2013 :  13:18:43  Show Profile  Reply with Quote
You mean besides the bad practice of converting proper datatypes to strings, letting SQL do implicit conversions and using a non ANSI/ISO date string?

Probably the main issue you could run into is if the server you are executing the query on a server that has different regional settings. I.E. - The date format DMY instead of MDY.

EDIT: Fat-fingered some text.

Edited by - Lamprey on 01/07/2013 15:57:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/07/2013 :  23:22:42  Show Profile  Reply with Quote
quote:
Originally posted by paulkem

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.


make sure you read this to understand issues in making date values varchar

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

also you cant use alias created in same query inside where like you showed above.
For using alias you need to wrap the SELECT...FROM part inside a derived table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

paulkem
Starting Member

25 Posts

Posted - 01/08/2013 :  06:52:19  Show Profile  Reply with Quote
Oh, I am aware that this is not the best way to do this. I am reviewing some older code. I think it was done this way as an attempt to get date time down to date only in SQL server 2005 where there was no date data type to CAST or CONVERT to.

I am also aware of the alias issue. The ... Was there to imply some other code that would make it possible. I should have been more clear.

My main concern is the comparison of the varchar MyDate in mm/dd/yyyy format. Would it be correctly evaluated against the true date data types of @Start and @End. Everything that I can find says that while this is definitely not the best way to do it, the comparisons should be accurate.

Pk

Pk

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/08/2013 :  23:38:51  Show Profile  Reply with Quote
quote:
Originally posted by paulkem

Oh, I am aware that this is not the best way to do this. I am reviewing some older code. I think it was done this way as an attempt to get date time down to date only in SQL server 2005 where there was no date data type to CAST or CONVERT to.

I am also aware of the alias issue. The ... Was there to imply some other code that would make it possible. I should have been more clear.

My main concern is the comparison of the varchar MyDate in mm/dd/yyyy format. Would it be correctly evaluated against the true date data types of @Start and @End. Everything that I can find says that while this is definitely not the best way to do it, the comparisons should be accurate.

Pk

Pk




you need to first convert them to proper dates before you do comparison with @start and @end. use CONVERT() function for that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/09/2013 :  21:44:36  Show Profile  Reply with Quote
quote:
Originally posted by paulkem

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.


You should be concerned about both at all times. If you don't think so, look at all the posts on this and other forums where people are asking how to speed up their code.

Your code is very close to being spot on depending, of course, on what you're trying to do. Will @StartDate and @EndDate be whole dates (dates with a midnight time)? If so, then one of the easiest, fastest, and most accurate ways to do the WHERE clause for such a thing would be as follows.
WHERE MyDate >= @StartDate and MyDate < DATEADD(dd,1,@EndDate)


--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

It's also "SARGable" which means that it can do Index Seeks if the right index is available.

Edited by - Jeff Moden on 01/09/2013 21:46:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000