January 27, 2014 at 9:55 am
A number of people have mentioned doing this without a loop. Here's an article on how to do it with a Tally table. from right here on SQL Server Central
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 27, 2014 at 11:14 am
Jeff Moden (1/23/2014)
PHYData DBA (1/22/2014)
Jeff,In reference to your comments about using date and time data types.
How would you recommend we take a date value and a time value and convert them to a smalldatetime or datetime value?
Or requirement is to do this with no explicit (executing the CAST or CONVERT functions) conversions on the data.
As JohnFTamburo pointed out, you shouldn't need explicit conversions and, most of the time, any implicit conversions amid all the different date/time datatypes should still allow index seeks.
Could you give me an example of how this should be done? Maybe you did not understand the questions...
How would YOU take the two different data type fields, Date and time, and create the correct datetime data type out-put value with only implicit conversions and no seek?
Would you use "+" or some other Coalesce to (for lack of a better term) join the to different data type values together to the different data type value?
Specifically something like this example except one that actually works:
DECLARE @date1 as date
,@time1 as time(2)
,@smalldate1 as smalldatetime
SELECT @date1 = '2013-01-01', @time1 = '16:20'
SELECT @date1 , @time1 = '16:20'
SELECT smalldate1 = @date1 + @Time1
NOTE: solution should not use a Tally table cause that is the same as using a dim table with strings, surrogate Key values, etc...
January 27, 2014 at 11:24 am
This will indeed not work:
declare @date1 as date = '2013-01-01';
declare @time1 as time(2) = '16:20';
select @date1 + @time1;
But this will:
declare @date1 as smalldatetime = '2013-01-01';
declare @time1 as time(2) = '16:20';
select @date1 + @time1;
As the l-value type is already a datetime and can hold the time part that is added.
You just found a case where it cannot be done without explicit conversion, this would be a good replication of your case that works:
declare @date1 as date = '2013-01-01';
declare @time1 as time(2) = '16:20';
select cast( @date1 as smalldatetime ) + @time1;
January 27, 2014 at 11:24 am
JohnFTamburo (1/22/2014)
PHYData DBA (1/22/2014)
Jeff,In reference to your comments about using date and time data types.
How would you recommend we take a date value and a time value and convert them to a smalldatetime or datetime value?
Or requirement is to do this with no explicit (executing the CAST or CONVERT functions) conversions on the data.
Implicit conversions appear to work well. On SQL 2008 SP3:
I won't comment about how silly I feel the "no explicit CAST or CONVERT" standard is...
Thanks
John.
John your example has not a single Date or Time data type in it. The question was about Date and Time Values.Did you mean to post some other code?
NOTE: You can feel silly all you want about the standard. When the queries run 10 seconds faster for every 10 thousand rows returned I do not feel silly at all.
January 27, 2014 at 11:28 am
PHYData DBA (1/27/2014)
Could you give me an example of how this should be done? Maybe you did not understand the questions...
How would YOU take the two different data type fields, Date and time, and create the correct datetime data type out-put value with only implicit conversions and no seek?
Would you use "+" or some other Coalesce to (for lack of a better term) join the to different data type values together to the different data type value?
Specifically something like this example except one that actually works:
(snip)
NOTE: solution should not use a Tally table cause that is the same as using a dim table with strings, surrogate Key values, etc...
This is roundabout because of the ridiculous requirement to not use CAST or CONVERT.
DECLARE @date1 as date
,@time1 as time(2)
,@date2 as smalldatetime
,@time2 as smalldatetime
,@smalldate1 as smalldatetime
SET @date1 = '2013-01-01'
SET @time1 = '16:20'
SET @date2 = @date1
SET @time2 = @time1
SET @smalldate1 = @date2 + @time2
SELECT @smalldate1
January 27, 2014 at 11:29 am
declare @time1 time(0)
,@date1 date
,@sdate smalldatetime
SELECT @time1 = '16:20', @date1 = '2013-04-20'
SELECT @sdate = @time1
select @date1 + @sdate
The above code works, but only because we are changing the time(0) value to a smalldatetime value before creating the output of the two values together. If I were select two columns from tables using a select statement I don't see how I could do this without two or more conversions.
January 27, 2014 at 11:32 am
JohnFTamburo (1/27/2014)
PHYData DBA (1/27/2014)
Could you give me an example of how this should be done? Maybe you did not understand the questions...
How would YOU take the two different data type fields, Date and time, and create the correct datetime data type out-put value with only implicit conversions and no seek?
Would you use "+" or some other Coalesce to (for lack of a better term) join the to different data type values together to the different data type value?
Specifically something like this example except one that actually works:
(snip)
NOTE: solution should not use a Tally table cause that is the same as using a dim table with strings, surrogate Key values, etc...
This is roundabout because of the ridiculous requirement to not use CAST or CONVERT.
DECLARE @date1 as date
,@time1 as time(2)
,@date2 as smalldatetime
,@time2 as smalldatetime
,@smalldate1 as smalldatetime
SET @date1 = '2013-01-01'
SET @time1 = '16:20'
SET @date2 = @date1
SET @time2 = @time1
SET @smalldate1 = @date2 + @time2
SELECT @smalldate1
So.... How does this work with selecting values from columns in a table? Using this would take at least two or more conversions per row on the returned record set.
Not really an example of code that performs better than what I am using is it.
January 27, 2014 at 11:36 am
PHYData DBA (1/27/2014)
John your example has not a single Date or Time data type in it. The question was about Date and Time Values.Did you mean to post some other code?
NOTE: You can feel silly all you want about the standard. When the queries run 10 seconds faster for every 10 thousand rows returned I do not feel silly at all.
An implicit conversion will likely not run any faster than an explicit conversion. Either way you are doing an ETL process. Conversions are part and parcel of the game. If your conversions cost you 1 second per 100 rows, I doubt that the core issue is explicit v. implicit conversions unless you are doing these in the "left" side of a WHERE clause.
Combining a date and a time into a smalldatetime for the purpose of joining a date dimension (which is after all the original purpose of this thread) seems to be unneeded for warehousing purposes; to the contrary, ETL would be expected to render a DATE type or an INT to be used as the FK reference to the date dimension.
Thanks
John.
January 27, 2014 at 11:42 am
PHYData DBA (1/27/2014)
So.... How does this work with selecting values from columns in a table? Using this would take at least two or more conversions per row on the returned record set. Not really an example of code that performs better than what I am using is it.
Perhaps it is time to consider that consolidating a DATE into a SMALLDATETIME is best done with adding a CAST of the DATE to SMALLDATETIME and a CAST of the time to SMALLDATETIME, and realize that SQL Server has no faster means to do this.
Otherwise please open a new topic and post everything according to Jeff Moden's suggestions to seek assistance, so we can see the whole issue.
Thanks
John.
January 27, 2014 at 11:43 am
peter-757102 (1/27/2014)
This will indeed not work:(code)
But this will:
(code)
As the l-value type is already a datetime and can hold the time part that is added.
You just found a case where it cannot be done without explicit conversion, this would be a good replication of your case that works:
(code)
I think I was not making the concept about doing this in a way that can select the data from tables without more than one Implicit conversion. We are seeing more and more use of these two data types in third party solutions. They are nice and I guess they can reduce storage and overhead.
If you have to return them as a DateTime value in a select statement good luck doing it without having to do at least one explicit conversion.
January 27, 2014 at 11:45 am
JohnFTamburo (1/27/2014)
PHYData DBA (1/27/2014)
So.... How does this work with selecting values from columns in a table? Using this would take at least two or more conversions per row on the returned record set. Not really an example of code that performs better than what I am using is it.
Perhaps it is time to consider that consolidating a DATE into a SMALLDATETIME is best done with adding a CAST of the DATE to SMALLDATETIME and a CAST of the time to SMALLDATETIME, and realize that SQL Server has no faster means to do this.
Otherwise please open a new topic and post everything according to Jeff Moden's suggestions to seek assistance, so we can see the whole issue.
Thanks
John.[/quote]
John,
I do not have an issue. Except with the short sighted comment and suggestion that was made.
I have a solution that works for me perfectly.
There were those that commented that there was a better solution, so I asked to see it.
This is the Forum discussion on My Article not yours, so were is the harm in that?
Maybe you can open a new topic and post your comments to it ehhh?
January 29, 2014 at 8:03 pm
Jeff Moden (1/21/2014)
[...]But why INT. For a calendar table, SMALLDATETIME would work just fine in a pre-2008 environment and certainly one of the newer DATE datatypes would work without the added complexity of having to lookup dates through a key instead of using them directly.
There are a couple of reasons for this in a typical Kimball-style dimensional warehouse:
1. All dimensions use a single surrogate integer key. Kimball/Ross argue that dimension keys should have no intrinsic meaning so that people always encode filters against the dimension rather than directly against the fact key. Of course, everyone ignores that and encodes date dimension keys as YYYYMMDD.
2. Dimensions also encode special values such as "no such value" (typically -1), "not applicable" (typically -2), etc. While you can achieve a similar effect using a date placeholder (such as 1900.01.01), it makes life a little easier when you know that those special values always have a specific key value, regardless of the dimension. By including the special values as dimension entries, users never have to deal with anything other than inner joins between facts and dimensions.
Most date and time dimensions include "banding" or ranges -- year and month, quarters, financial versus calendar, etc. With some nice indexing over the ranges and including the dimension key, filtering a fact by a date or time range can be very very quick. Filters like "every Monday over the last three months that was not a holiday" become easy to write and perform well, even for novice SQL coders. I think it's difficult to get the same performance benefits using a function, although you could use an indexed view. I use a table rather than a view simply because all of the other dimensions are tables, so I don't see any value in making an exception.
I must admit when I populate date and time dimensions, I just use a top row_number() from sys.columns and then dateadd() to generate the date/time values.
January 29, 2014 at 8:08 pm
Bruce W Cassidy (1/29/2014)
Most date and time dimensions include "banding" or ranges -- year and month, quarters, financial versus calendar, etc. With some nice indexing over the ranges and including the dimension key, filtering a fact by a date or time range can be very very quick. Filters like "every Monday over the last three months that was not a holiday" become easy to write and perform well, even for novice SQL coders. I think it's difficult to get the same performance benefits using a function, although you could use an indexed view. I use a table rather than a view simply because all of the other dimensions are tables, so I don't see any value in making an exception.I must admit when I populate date and time dimensions, I just use a top row_number() from sys.columns and then dateadd() to generate the date/time values.
I just use an identity in the date dimension and refer to the identity. However, I agree that it is best to use an integer that contains no intrinsic data.
Thanks
John.
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply