January 8, 2013 at 8:31 am
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.
[Emphasis added]
NO, you won't, if the column can include an actual time. Please review my earlier post on the dangers of "<=" with datetime 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 8, 2013 at 8:41 am
ScottPletcher: Sorry, i meant to say "My concern was the comparison of a varchar date with an "actual" DATE data type."
January 8, 2013 at 9:06 am
ScottPletcher (1/8/2013)
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.
[Emphasis added]
NO, you won't, if the column can include an actual time. Please review my earlier post on the dangers of "<=" with datetime types.
The conversion will strip the time out. Look at the sample code provided. Yes, it's DateTime, but the time is forced to 0 milliseconds from midnight by the conversion to a formatted string that doesn't have a time element, then the implicit conversion back to DateTime.
If you don't believe/understand that, look at the sample test code I posted (page 1 of this thread, after the initial post). Run it. You'll see it do EXACTLY what I say it will do, which is include the final date, despite the final date in the sample data having a time value of 23:00. Run the code, you'll see it not matter that the column has a time value in it.
Because of the conversion, this code doesn't actually check the column. It checks against the result of the conversion, which has been forced to have a static time value. Since 0 = 0 for all cases, the <= comparison works consistently for all cases.
Again, the problem here isn't whether it will get the right results. It will do that. Test it if you don't believe me. Test it however many times, using whatever permutations you like, but test using the exact code that was posted. You'll get exactly the results I predicted here. Every time. No exceptions.
What you're addressing is a different problem. A valid one, but not the question/issue that's being presented here.
- 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 8, 2013 at 10:59 am
That's why I cast the date as an int. The fractional part of the date is the time. Casting is pretty fast.
January 8, 2013 at 11:16 am
lnardozi 61862 (1/8/2013)
That's why I cast the date as an int. The fractional part of the date is the time. Casting is pretty fast.
Rounding errors.
Try this:
DECLARE @D1 DATETIME = '2013-01-08 13:00', @D2 DATETIME = '2013-01-08 11:00';
SELECT CAST(@D1 AS INT), CAST(@D2 AS INT);
You'll get 41281 for the one with a PM time, 41280 for the AM time, for the same date. So it's not really valid for the kind of calculations being done here.
- 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 8, 2013 at 11:23 am
If you really need the numeric part of the date, you can do either of these:
DECLARE @D1 DATETIME = '2013-01-08 13:00', @D2 DATETIME = '2013-01-08 11:00';
SELECT FLOOR(CAST(@D1 AS FLOAT)), FLOOR(CAST(@D2 AS FLOAT));
or
DECLARE @D1 DATETIME = '2013-01-08 13:00', @D2 DATETIME = '2013-01-08 11:00';
SELECT DATEDIFF(DAY, 0, @D1), DATEDIFF(DAY, 0, @D2);
Generally, if I'm going to do "date/time type stuff" with the data, I would use the DateDiff version, because it makes it more obvious what you're doing. It's also future-proof, since DateDiff is deterministic and documented, while casting DateTime/Date/DateTime2/DateTimeOffset to Int/Float is iffy.
DateTime and SmallDateTime can be directly cast/converted to Int or Float, but DateTime2, Date, Time, DateTimeOffset can't (per http://msdn.microsoft.com/en-us/library/ms187928.aspx). So, if a column datatype ever needs to be refactored, and is converted to a non-convertable datatype, you'll have to re-write code if you're using a cast/convert to Int/Float. You won't have to rewrite any of it if you use DateDiff instead.
- 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 8, 2013 at 4:17 pm
paul.j.kemna (1/7/2013)
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?
There can still be an issue with the setting of DATEFORMAT, so it is better to stay away from it:
declare @test_date datetime
declare @varchar_date varchar(20)
set @test_date = '20121215'
select @varchar_date = convert(varchar(20),@test_date,101)
select [@varchar_date] = @varchar_date
set dateformat dmy
select Back_To_Datetime = convert(datetime,@varchar_date)
Results:
@varchar_date
--------------------
12/15/2012
(1 row(s) affected)
Back_To_Datetime
-----------------------
Msg 242, Level 16, State 3, Line 13
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
January 9, 2013 at 4:26 am
Aargh! I knew that! Failure to engage brain before engaging fingers!
ROUND(@date,0,1)
is what I meant.
January 9, 2013 at 11:12 am
Michael Valentine Jones (1/8/2013)
paul.j.kemna (1/7/2013)
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?
There can still be an issue with the setting of DATEFORMAT, so it is better to stay away from it:
declare @test_date datetimedeclare @varchar_date varchar(20)
set @test_date = '20121215'
select @varchar_date = convert(varchar(20),@test_date,101)
select [@varchar_date] = @varchar_date
set dateformat dmy
select Back_To_Datetime = convert(datetime,@varchar_date)
Results:
@varchar_date
--------------------
12/15/2012
(1 row(s) affected)
Back_To_Datetime
-----------------------
Msg 242, Level 16, State 3, Line 13
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Unusual to set the date format differently in different parts of the same script/proc/UDF, but it could happen. That's definitely one of the real problems here, and why date datatype functions (like DateAdd, DateDiff, et al) should be used instead.
- 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 9, 2013 at 11:18 am
lnardozi 61862 (1/9/2013)
Aargh! I knew that! Failure to engage brain before engaging fingers!
ROUND(@date,0,1)
is what I meant.
That still doesn't get around the implicit conversion issues I pointed out.
Try this:
DECLARE @Date DATETIME2 = GETDATE();
SELECT ROUND(@Date, 0, 1);
You'll get an error.
That's why it should be:
DECLARE @Date DATETIME2 = GETDATE();
SELECT DATEDIFF(DAY, '1900-01-01', @Date);
Or you can use 0 instead of the explicit date for your base-date. Or pick a date that has characteristics that work better for you, like a Sunday if you want to use the function for DateDiff(Week) to get day-of-week.
Either way, it's better than relying on implicit conversion.
- 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 10, 2013 at 2:19 am
You learn something new every day (if you're lucky).
Had no idea it was incompatible with DateTime2.
January 11, 2013 at 12:17 pm
Am I missing something? SQL Server 2008 has the "date" type; casting a datetime to date will remove the time component and then you can compare dates with dates, rather than fiddling around with dates in string format.
January 11, 2013 at 12:59 pm
The original query was actually written in 2005 where the DATE data type did not exist.
January 11, 2013 at 1:24 pm
And you often need to compare for a full day against a legitimate datetime column.
For example, in a logging table, you would have logging datetime -- obviously you would want the time.
But you could want to query the table for all rows for a given day:
WHERE
logging_datetime >= '20130111' AND
logging_datetime < '20130112'
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 11, 2013 at 2:04 pm
paul.j.kemna (1/11/2013)
The original query was actually written in 2005 where the DATE data type did not exist.
Ah. I didn't spot that, since you posted in a SQL Server 2008 group.
The combination I learned (from this site) was:
dateadd(day, datediff(day, 0, @datetimeVariable), 0)
It's quicker than converting to a string, and you can still compare the datetimes to each other.
The thing I still use about that trick is that you can replace "day" with any other unit, such as minute, hour, month or quarter.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply