Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
paulkem
Starting Member
28 Posts |
 Posted - 2013-01-07 : 12:24:50
|
| 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 dateSET @StartDate = some calculated dateDECLARE @EndDate as dateSET @EndDate = another calculated dateCONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate ...WHERE MyDate >= @StartDate and MyDate <= @EndDateI am not so concerned about efficiency as I am accuracy at this time. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-07 : 13:13:53
|
| 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?JimP.S. You can't use MyDate in the WHERE clause if MyDate is determinedin the SELECT clauseEveryday I learn something that somebody else already knew |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-07 : 13:18:43
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-07 : 23:22:42
|
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 dateSET @StartDate = some calculated dateDECLARE @EndDate as dateSET @EndDate = another calculated dateCONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate ...WHERE MyDate >= @StartDate and MyDate <= @EndDateI 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 varcharhttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.htmlalso 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
paulkem
Starting Member
28 Posts |
Posted - 2013-01-08 : 06:52:19
|
| 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.PkPk |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 23:38:51
|
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.PkPk
you need to first convert them to proper dates before you do comparison with @start and @end. use CONVERT() function for that.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-09 : 21:44:36
|
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 dateSET @StartDate = some calculated dateDECLARE @EndDate as dateSET @EndDate = another calculated dateCONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate ...WHERE MyDate >= @StartDate and MyDate <= @EndDateI 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. |
|
|
|
|
|
|
|
|
Date Comparisons