January 7, 2013 at 10:12 am
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?
January 7, 2013 at 10:15 am
If you mean something like:
WHERE MyDateTimeColumn >= CONVERT(VARCHAR(25), MyOtherDateTimeColumn, 101)
AND MyDateTimeColumn < CONVERT(VARCHAR(25), YetAnotherDateTimeColumn, 101)
Then, yes, that will work. It'll be inefficient, but it will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 10:23 am
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.
January 7, 2013 at 12:35 pm
paul.j.kemna (1/7/2013)
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 use "<" rather than "<=" on the @EndDate comparison:
WHERE MyDate >= @StartDate and MyDate < @EndDate
Adjust @EndDate accordingly before the SQL statement. For example, if you're working with days, set @EndDate to the next day at midnight. If you're working with minutes, set @EndDate to the next minute.
That makes sure the comparisons remain accurate for all different date/datetime data types.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2013 at 12:49 pm
paul.j.kemna (1/7/2013)
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.
Why use the Convert() function on the column in the Where clause? (For that matter, why use it in the Select clause? Date formatting should be done in the presentation layer, not the DAL. But that's beyond the scope of the original question.)
Or is that not what you meant here? You're not converting in your Where clause, and I'm misreading something?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 1:07 pm
No, I know what I SHOULD do. I am looking at previous processes and just need to know if this query would return the correct results. All of my testing indicates that it would, but I just want to hear it from someone else.
January 7, 2013 at 1:25 pm
Assuming "correct results" means dates between the two variable values, including the end-date value, then yes.
As mentioned, using <= @EndDate can give odd results.
For example:
DECLARE @T TABLE
(ID INT IDENTITY
PRIMARY KEY,
DT DATETIME);
INSERT INTO @T
(DT)
VALUES ('2013-01-01 06:00'),
('2013-01-02 08:00'),
('2013-01-03 23:00');
DECLARE @StartDate DATE = '2013-01-02',
@EndDate DATE = '2013-01-03';
SELECT *
FROM @T
WHERE CONVERT(CHAR(10), DT, 101) >= @StartDate
AND CONVERT(CHAR(10), DT, 101) <= @EndDate;
If "End Date" means "everything up to but not including", then this will give incorrect results. If it means everything up to and including, then it will give correct results.
So whether that will be correct or not depends on your definition of End Date.
If, for example, you want all of January 2013 in a query, then you have to assign Start Date as 1 Jan 13, and End Date as 31 Jan 13. It's a lot easier to calculate the first day of the next month, and use < instead of <=, because that skips the complexity of dealing with variable end dates (31 for Jan, 28 for Feb except when it's 29, 31 for March, 30 for April, etc.).
That's why most prefer to use >= Start Date, and < End Date, and define End Date as the "up to but not including". Then for January, you figure out the 1st of the month (dead easy), and you use DateAdd to add one month to it (again, dead easy), and you're done. No worrying about short/long/messed-up months that we inherited from Romans who couldn't figure out how to make December always be in the winter or just plain didn't care about that.
So I can't tell if it's correct or not, because I don't know how End Date is defined in your system.
Beyond that, yeah, it'll work correctly if End Date is defined as "up to and including".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 1:31 pm
Wow, that makes a lot of sense. Such a simple concept that i may have overlooked in some places.
But in this case, we are assuming that I DO want to include EndDate. My concern was the comparison of a varchar date with an "actual" datetime data type.
January 7, 2013 at 1:34 pm
Apropos of nothing, my favorite is
declare @i int
select @i=CONVERT(int,cast('01/07/2013' as datetime) )
SELECT * FROM table where
CAST(field AS int) = @i
It's very fast.
January 7, 2013 at 1:37 pm
GSquared (1/7/2013)
Assuming "correct results" means dates between the two variable values, including the end-date value, then yes.As mentioned, using <= @EndDate can give odd results.
For example:
DECLARE @T TABLE(ID INT IDENTITY
PRIMARY KEY,
DT DATETIME);
INSERT INTO @T
(DT)
VALUES ('2013-01-01 06:00'),
('2013-01-02 08:00'),
('2013-01-03 23:00');
DECLARE @StartDate DATE = '2013-01-02',
@EndDate DATE = '2013-01-03';
SELECT *
FROM @T
WHERE CONVERT(CHAR(10), DT, 101) >= @StartDate
AND CONVERT(CHAR(10), DT, 101) <= @EndDate;
If "End Date" means "everything up to but not including", then this will give incorrect results. If it means everything up to and including, then it will give correct results.
So whether that will be correct or not depends on your definition of End Date.
If, for example, you want all of January 2013 in a query, then you have to assign Start Date as 1 Jan 13, and End Date as 31 Jan 13. It's a lot easier to calculate the first day of the next month, and use < instead of <=, because that skips the complexity of dealing with variable end dates (31 for Jan, 28 for Feb except when it's 29, 31 for March, 30 for April, etc.).
That's why most prefer to use >= Start Date, and < End Date, and define End Date as the "up to but not including". Then for January, you figure out the 1st of the month (dead easy), and you use DateAdd to add one month to it (again, dead easy), and you're done. No worrying about short/long/messed-up months that we inherited from Romans who couldn't figure out how to make December always be in the winter or just plain didn't care about that.
So I can't tell if it's correct or not, because I don't know how End Date is defined in your system.
Beyond that, yeah, it'll work correctly if End Date is defined as "up to and including".
Yes, the month-end day issues are a strong consideration.
But from my standpoint, time is the more critical component than even the day when using <=.
Most people by default use <=, say for monthly totals, like so:
WHERE datetime_column >= '20120101' AND date_column <= '20120131'
WHERE datetime_column >= '20120201' AND date_column <= '20120229'
Look OK? No! What about rows with datetimes of '20120131 04:23' and '20120131 17:22', etc.? They are not included!
Ah, but you've thought of that, and you'll get around it like so, for your current "datetime" column:
WHERE datetime_column >= '20120101' AND datetime_column <= '20120131 23:59:59.997'
Ooh, problematic already -- note that you have to precisely specify the time -- .998 or .999 will round up to the next day.
But let's say you get past that. Then along comes SQL 2008 and "datetime2". Suddenly they need more accurate times for orders, so "datetime_column" gets changed to "datetime2".
The query is now wrong again! The "<=" time needs to be '20120131 23:59:59.9999999' (IIRC). So rows with times from "20120131 23:59:59.9971" thru .9999999 are simply ignored until/unless the query is changed.
Then, after a month, what if they decide to go back to "datetime"? Argghh!
Smalldatetime has similar issues, of course.
But note that "<" the next range value handles all of this correctly.
You stated you most wanted accuracy -- I agree 100%. And that's why I stated you should switch to the "<" method ;-).
Edit: .998 on datetime rounds down, not up; .999 does round up to the next day.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2013 at 1:42 pm
paul.j.kemna (1/7/2013)
Wow, that makes a lot of sense. Such a simple concept that i may have overlooked in some places.But in this case, we are assuming that I DO want to include EndDate. My concern was the comparison of a varchar date with an "actual" datetime data type.
Since SQL Server will implicitly convert the string back into a date for the comparison, you'll be fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 1:49 pm
ScottPletcher (1/7/2013)
GSquared (1/7/2013)
Assuming "correct results" means dates between the two variable values, including the end-date value, then yes.As mentioned, using <= @EndDate can give odd results.
For example:
DECLARE @T TABLE(ID INT IDENTITY
PRIMARY KEY,
DT DATETIME);
INSERT INTO @T
(DT)
VALUES ('2013-01-01 06:00'),
('2013-01-02 08:00'),
('2013-01-03 23:00');
DECLARE @StartDate DATE = '2013-01-02',
@EndDate DATE = '2013-01-03';
SELECT *
FROM @T
WHERE CONVERT(CHAR(10), DT, 101) >= @StartDate
AND CONVERT(CHAR(10), DT, 101) <= @EndDate;
If "End Date" means "everything up to but not including", then this will give incorrect results. If it means everything up to and including, then it will give correct results.
So whether that will be correct or not depends on your definition of End Date.
If, for example, you want all of January 2013 in a query, then you have to assign Start Date as 1 Jan 13, and End Date as 31 Jan 13. It's a lot easier to calculate the first day of the next month, and use < instead of <=, because that skips the complexity of dealing with variable end dates (31 for Jan, 28 for Feb except when it's 29, 31 for March, 30 for April, etc.).
That's why most prefer to use >= Start Date, and < End Date, and define End Date as the "up to but not including". Then for January, you figure out the 1st of the month (dead easy), and you use DateAdd to add one month to it (again, dead easy), and you're done. No worrying about short/long/messed-up months that we inherited from Romans who couldn't figure out how to make December always be in the winter or just plain didn't care about that.
So I can't tell if it's correct or not, because I don't know how End Date is defined in your system.
Beyond that, yeah, it'll work correctly if End Date is defined as "up to and including".
Yes, the month-end day issues are a strong consideration.
But from my standpoint, time is the more critical component than even the day when using <=.
Most people by default use <=, say for monthly totals, like so:
WHERE datetime_column >= '20120101' AND date_column <= '20120131'
WHERE datetime_column >= '20120201' AND date_column <= '20120229'
Look OK? No! What about rows with datetimes of '20120131 04:23' and '20120131 17:22', etc.? They are not included!
Ah, but you've thought of that, and you'll get around it like so, for your current "datetime" column:
WHERE datetime_column >= '20120101' AND datetime_column <= '20120131 23:59:59.997'
Ooh, problematic already -- note that you have to precisely specify the time -- .998 or .999 will round up to the next day.
But let's say you get past that. Then along comes SQL 2008 and "datetime2". Suddenly they need more accurate times for orders, so "datetime_column" gets changed to "datetime2".
The query is now wrong again! The "<=" time needs to be '20120131 23:59:59.9999999' (IIRC). So rows with times from "20120131 23:59:59.9971" thru .9999999 are simply ignored until/unless the query is changed.
Then, after a month, what if they decide to go back to "datetime"? Argghh!
Smalldatetime has similar issues, of course.
But note that "<" the next range value handles all of this correctly.
You stated you most wanted accuracy -- I agree 100%. And that's why I stated you should switch to the "<" method ;-).
Edit: .998 on datetime rounds down, not up; .999 does round up to the next day.
In this particular case, where they're converting from DateTime to a format that doesn't include the time, it will work without the issues you brought up.
There are MUCH better ways to do that, but Paul already mentioned he knows how it should be done (as opposed to how it's being done), so that shouldn't be an issue.
The whole "convert to format 101" is how a lot of devs work around those exact issues you mention, where midnight can be rounded to or missed. They do it because they don't know better methods, even though those methods have been around for over a decade. But it does work. (So does using a pogo stick to commute. Might even be fun. But there are methods that most people find "better".) 😛
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 1:56 pm
GSquared (1/7/2013)
ScottPletcher (1/7/2013)
GSquared (1/7/2013)
Assuming "correct results" means dates between the two variable values, including the end-date value, then yes.As mentioned, using <= @EndDate can give odd results.
For example:
DECLARE @T TABLE(ID INT IDENTITY
PRIMARY KEY,
DT DATETIME);
INSERT INTO @T
(DT)
VALUES ('2013-01-01 06:00'),
('2013-01-02 08:00'),
('2013-01-03 23:00');
DECLARE @StartDate DATE = '2013-01-02',
@EndDate DATE = '2013-01-03';
SELECT *
FROM @T
WHERE CONVERT(CHAR(10), DT, 101) >= @StartDate
AND CONVERT(CHAR(10), DT, 101) <= @EndDate;
If "End Date" means "everything up to but not including", then this will give incorrect results. If it means everything up to and including, then it will give correct results.
So whether that will be correct or not depends on your definition of End Date.
If, for example, you want all of January 2013 in a query, then you have to assign Start Date as 1 Jan 13, and End Date as 31 Jan 13. It's a lot easier to calculate the first day of the next month, and use < instead of <=, because that skips the complexity of dealing with variable end dates (31 for Jan, 28 for Feb except when it's 29, 31 for March, 30 for April, etc.).
That's why most prefer to use >= Start Date, and < End Date, and define End Date as the "up to but not including". Then for January, you figure out the 1st of the month (dead easy), and you use DateAdd to add one month to it (again, dead easy), and you're done. No worrying about short/long/messed-up months that we inherited from Romans who couldn't figure out how to make December always be in the winter or just plain didn't care about that.
So I can't tell if it's correct or not, because I don't know how End Date is defined in your system.
Beyond that, yeah, it'll work correctly if End Date is defined as "up to and including".
Yes, the month-end day issues are a strong consideration.
But from my standpoint, time is the more critical component than even the day when using <=.
Most people by default use <=, say for monthly totals, like so:
WHERE datetime_column >= '20120101' AND date_column <= '20120131'
WHERE datetime_column >= '20120201' AND date_column <= '20120229'
Look OK? No! What about rows with datetimes of '20120131 04:23' and '20120131 17:22', etc.? They are not included!
Ah, but you've thought of that, and you'll get around it like so, for your current "datetime" column:
WHERE datetime_column >= '20120101' AND datetime_column <= '20120131 23:59:59.997'
Ooh, problematic already -- note that you have to precisely specify the time -- .998 or .999 will round up to the next day.
But let's say you get past that. Then along comes SQL 2008 and "datetime2". Suddenly they need more accurate times for orders, so "datetime_column" gets changed to "datetime2".
The query is now wrong again! The "<=" time needs to be '20120131 23:59:59.9999999' (IIRC). So rows with times from "20120131 23:59:59.9971" thru .9999999 are simply ignored until/unless the query is changed.
Then, after a month, what if they decide to go back to "datetime"? Argghh!
Smalldatetime has similar issues, of course.
But note that "<" the next range value handles all of this correctly.
You stated you most wanted accuracy -- I agree 100%. And that's why I stated you should switch to the "<" method ;-).
Edit: .998 on datetime rounds down, not up; .999 does round up to the next day.
In this particular case, where they're converting from DateTime to a format that doesn't include the time, it will work without the issues you brought up.
There are MUCH better ways to do that, but Paul already mentioned he knows how it should be done (as opposed to how it's being done), so that shouldn't be an issue.
The whole "convert to format 101" is how a lot of devs work around those exact issues you mention, where midnight can be rounded to or missed. They do it because they don't know better methods, even though those methods have been around for over a decade. But it does work. (So does using a pogo stick to commute. Might even be fun. But there are methods that most people find "better".) 😛
That remains true only if the column is never converted to a data type that includes time. I suppose one can make that assumption, since in that case all existing code would have to checked anyway. But I'll stick with a method that always works regardless of future date/datetime conversions :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 7, 2013 at 7:42 pm
GSquared (1/7/2013)
paul.j.kemna (1/7/2013)
Wow, that makes a lot of sense. Such a simple concept that i may have overlooked in some places.But in this case, we are assuming that I DO want to include EndDate. My concern was the comparison of a varchar date with an "actual" datetime data type.
Since SQL Server will implicitly convert the string back into a date for the comparison, you'll be fine.
That is what i wanted to hear, thanks. This was very educational, even outside the scope of my question!
For what it's worth, in most cases this code was used in SQL Server 2005 as a way to convert to "date only" since there was no date data type. (I still recognize there are better ways).
January 8, 2013 at 6:59 am
ScottPletcher (1/7/2013)
GSquared (1/7/2013)
ScottPletcher (1/7/2013)
GSquared (1/7/2013)
Assuming "correct results" means dates between the two variable values, including the end-date value, then yes.As mentioned, using <= @EndDate can give odd results.
For example:
DECLARE @T TABLE(ID INT IDENTITY
PRIMARY KEY,
DT DATETIME);
INSERT INTO @T
(DT)
VALUES ('2013-01-01 06:00'),
('2013-01-02 08:00'),
('2013-01-03 23:00');
DECLARE @StartDate DATE = '2013-01-02',
@EndDate DATE = '2013-01-03';
SELECT *
FROM @T
WHERE CONVERT(CHAR(10), DT, 101) >= @StartDate
AND CONVERT(CHAR(10), DT, 101) <= @EndDate;
If "End Date" means "everything up to but not including", then this will give incorrect results. If it means everything up to and including, then it will give correct results.
So whether that will be correct or not depends on your definition of End Date.
If, for example, you want all of January 2013 in a query, then you have to assign Start Date as 1 Jan 13, and End Date as 31 Jan 13. It's a lot easier to calculate the first day of the next month, and use < instead of <=, because that skips the complexity of dealing with variable end dates (31 for Jan, 28 for Feb except when it's 29, 31 for March, 30 for April, etc.).
That's why most prefer to use >= Start Date, and < End Date, and define End Date as the "up to but not including". Then for January, you figure out the 1st of the month (dead easy), and you use DateAdd to add one month to it (again, dead easy), and you're done. No worrying about short/long/messed-up months that we inherited from Romans who couldn't figure out how to make December always be in the winter or just plain didn't care about that.
So I can't tell if it's correct or not, because I don't know how End Date is defined in your system.
Beyond that, yeah, it'll work correctly if End Date is defined as "up to and including".
Yes, the month-end day issues are a strong consideration.
But from my standpoint, time is the more critical component than even the day when using <=.
Most people by default use <=, say for monthly totals, like so:
WHERE datetime_column >= '20120101' AND date_column <= '20120131'
WHERE datetime_column >= '20120201' AND date_column <= '20120229'
Look OK? No! What about rows with datetimes of '20120131 04:23' and '20120131 17:22', etc.? They are not included!
Ah, but you've thought of that, and you'll get around it like so, for your current "datetime" column:
WHERE datetime_column >= '20120101' AND datetime_column <= '20120131 23:59:59.997'
Ooh, problematic already -- note that you have to precisely specify the time -- .998 or .999 will round up to the next day.
But let's say you get past that. Then along comes SQL 2008 and "datetime2". Suddenly they need more accurate times for orders, so "datetime_column" gets changed to "datetime2".
The query is now wrong again! The "<=" time needs to be '20120131 23:59:59.9999999' (IIRC). So rows with times from "20120131 23:59:59.9971" thru .9999999 are simply ignored until/unless the query is changed.
Then, after a month, what if they decide to go back to "datetime"? Argghh!
Smalldatetime has similar issues, of course.
But note that "<" the next range value handles all of this correctly.
You stated you most wanted accuracy -- I agree 100%. And that's why I stated you should switch to the "<" method ;-).
Edit: .998 on datetime rounds down, not up; .999 does round up to the next day.
In this particular case, where they're converting from DateTime to a format that doesn't include the time, it will work without the issues you brought up.
There are MUCH better ways to do that, but Paul already mentioned he knows how it should be done (as opposed to how it's being done), so that shouldn't be an issue.
The whole "convert to format 101" is how a lot of devs work around those exact issues you mention, where midnight can be rounded to or missed. They do it because they don't know better methods, even though those methods have been around for over a decade. But it does work. (So does using a pogo stick to commute. Might even be fun. But there are methods that most people find "better".) 😛
That remains true only if the column is never converted to a data type that includes time. I suppose one can make that assumption, since in that case all existing code would have to checked anyway. But I'll stick with a method that always works regardless of future date/datetime conversions :-).
(emphasis added)
No, that doesn't matter in the slightest. Converting to format 101 pulls the time off. That's the whole point. Thus, the code will still work. It will work inefficiently, but it will work correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply