September 4, 2015 at 7:50 am
I tried this:
SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))
Result: 2015-09-04 16:00:00
It works (FnDateOnly strips the time).
Is there a more efficient way ?
September 4, 2015 at 7:52 am
mar.ko (9/4/2015)
I tried this:SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))
Result: 2015-09-04 16:00:00
It works (FnDateOnly strips the time).
Is there a more efficient way ?
What are you trying to do? And what is fnDateOnly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2015 at 8:13 am
Sean Lange (9/4/2015)
mar.ko (9/4/2015)
I tried this:SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))
Result: 2015-09-04 16:00:00
It works (FnDateOnly strips the time).
Is there a more efficient way ?
What are you trying to do? And what is fnDateOnly?
RTFM: (FnDateOnly strips the time) ex: fnDateOnly('2015-09-04 16:00:00') = 2015-09-04 00:00:00
I am trying to create a datetime dimension table with a key of hour. So I need to go thru the next 10 years and create a row for every hour.
DateTimeKey is smalldatetime format. My technique would not work with datetime format....
September 4, 2015 at 8:13 am
If you just want to show the date and time with hour, minute and second you can use:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss')
MM = month
mm = minute
September 4, 2015 at 8:28 am
Thanks for that...forgot about FORMAT.
The final solution:
SELECT Convert(smalldatetime,FORMAT(GETDATE(), 'yyyy-MM-dd hh:00:00'))
In fact that works with datetime format as well.
My solution was too convoluted....however, since no string conversion was involved, it may have been a tad more efficient than this one.
September 4, 2015 at 8:30 am
you can also use the dateadd/datediff functions to strip to the hour, and still keep the value as a datetime instead of a varchar.
select DATEADD(hh, DATEDIFF(hh,0,getdate()), 0) --2015-09-04 10:00:00.000
Lowell
September 4, 2015 at 8:32 am
mar.ko (9/4/2015)
Sean Lange (9/4/2015)
mar.ko (9/4/2015)
I tried this:SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))
Result: 2015-09-04 16:00:00
It works (FnDateOnly strips the time).
Is there a more efficient way ?
What are you trying to do? And what is fnDateOnly?
RTFM: (FnDateOnly strips the time) ex: fnDateOnly('2015-09-04 16:00:00') = 2015-09-04 00:00:00
I am trying to create a datetime dimension table with a key of hour. So I need to go thru the next 10 years and create a row for every hour.
DateTimeKey is smalldatetime format. My technique would not work with datetime format....
Let's keep this professional. There is no reason for you to tell me to RTFM. Can you show me where in the manual that function is? That is NOT a build in function of sql server.
What you are trying to accomplish is actually really simple using a tally table.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E5(N) AS (SELECT 1 FROM E4 a, E1 b),
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E5
)
select DATEADD(HOUR, N, dateadd(hour, datediff(hour, 0, GETDATE()), 0))
from cteTally
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2015 at 9:23 am
mar.ko (9/4/2015)
Sean Lange (9/4/2015)
mar.ko (9/4/2015)
I tried this:SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))
Result: 2015-09-04 16:00:00
It works (FnDateOnly strips the time).
Is there a more efficient way ?
What are you trying to do? And what is fnDateOnly?
RTFM: (FnDateOnly strips the time) ex: fnDateOnly('2015-09-04 16:00:00') = 2015-09-04 00:00:00
I am trying to create a datetime dimension table with a key of hour. So I need to go thru the next 10 years and create a row for every hour.
DateTimeKey is smalldatetime format. My technique would not work with datetime format....
Telling someone to "RTFM" isn't professional nor is it the way to make friends on this or any other forum.
You should also continue to forget about FORMAT in SQL Server because it's been tested to be 44 times slower than any other method that you'll find to do the same thing. Lowell and Sean have both given you solutions that will blow the doors off of the use of FORMAT. In the same vein as your post, perhaps if you did a little RTFS and some testing, you'd know that. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2015 at 11:38 am
Sorry guys, my bad....definitely having a bad day.
And DateAdd is probably efficient....as whenever strings are involved, the conversion is usually costly.
Lowell's solution is very slick and efficient.
Sean's solution is way out of my league...I can't even comprehend what is going on there.
I'll have to study it.
Here is my little function to strip the time.
CREATE FUNCTION fnDateOnly
(
@FullDate datetime
)
RETURNS datetime
AS
BEGIN
-- Return the result of the function
RETURN CAST(FLOOR(CAST(@FullDate AS FLOAT)) AS DATETIME)
END
September 4, 2015 at 12:10 pm
Sean is using cascading CTEs to create a tally table. A tally table allows you to work with rows that are numbered.
In this case, Sean included 100 000 rows that will generate days and hours for approximately 11.5 years, that will begin from today.
I'd changed his code to create 2 different versions that might be easier to get thanks to the comments and a little simplicity added.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), --10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10 x 10 = 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --100 x 100 = 10,000 rows
E5(N) AS (SELECT 1 FROM E4 a, E1 b), --10,000 x 10 = 100,000 rows
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 --Start from 0
FROM E5
)
select DATEADD(HOUR, N, '20000101') DateHour --Start from year 2000
from cteTally
ORDER BY DateHour;
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), --10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10 x 10 = 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --100 x 100 = 10,000 rows
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 --Start from 0
FROM E4
)
select DATEADD( HOUR, H.N, DATEADD(DAY, D.N, '20000101')) DateHour --Start from year 2000
from cteTally D --Used for Days
CROSS
JOIN cteTally H --Used for Hours
WHERE H.N < 24 --Limit to 24 hours. No limit is set to the days, but it could be added as well.
ORDER BY DateHour;
For more about tally tables: http://www.sqlservercentral.com/articles/T-SQL/62867/
September 4, 2015 at 12:13 pm
mar.ko (9/4/2015)
Sorry guys, my bad....definitely having a bad day.And DateAdd is probably efficient....as whenever strings are involved, the conversion is usually costly.
Lowell's solution is very slick and efficient.
Sean's solution is way out of my league...I can't even comprehend what is going on there.
I'll have to study it.
Here is my little function to strip the time.
CREATE FUNCTION fnDateOnly
(
@FullDate datetime
)
RETURNS datetime
AS
BEGIN
-- Return the result of the function
RETURN CAST(FLOOR(CAST(@FullDate AS FLOAT)) AS DATETIME)
END
The biggest performance issue I see here is using a scalar function. There are notoriously awful performers. The other challenge is you stated you wanted a row for each hour of each day for the next 10 years. The function you posted isn't going to help much because it forces the time to the same value so you can't get this to produce the hours.
As for my solution being out of our league let's see if we can help a bit. It isn't as complicated as it seems. If you tried it, it will produce a datetime value for every hour starting at the current hour for the next 100,000 hours. You can easily adjust this a little bit to produce the exact results you need. Did you happen to notice how fast it is? Because it is a zero read method of creating this cte, it will return those 100,000 sequential dates almost instantly. Now THAT is efficient!!
To help with the logic a little bit, the ctes are just generating a table with 100,000 sequential values. This is commonly referred to as a numbers or tally table. It makes this type of thing really easy. I would go into more detail but thankfully our friend Jeff Moden has an amazing article on this very topic right on SSC. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
The tally table has been dubbed the "Swiss army knife of sql" around here. IIRC the original moniker came from Gianluca. If you read and understand the concepts in that article it will, or least should, completely change the way you look at and think about data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2015 at 12:27 pm
Thanks Luis for the explanation.....it's very slick.
I like to call these things "patterns" ...as they can be reused to solve similar problems.
September 4, 2015 at 3:49 pm
mar.ko (9/4/2015)
And DateAdd is probably efficient....as whenever strings are involved, the conversion is usually costly.
Lowell's solution is very slick and efficient.
Sean's solution is way out of my league...I can't even comprehend what is going on there.
I'll have to study it.
MUCH Better! 😛 We've all been there. As "Red Green" would say, "We're all in this together and I'm pullin' for ya".
Here is my little function to strip the time.
CREATE FUNCTION fnDateOnly
(
@FullDate datetime
)
RETURNS datetime
AS
BEGIN
-- Return the result of the function
RETURN CAST(FLOOR(CAST(@FullDate AS FLOAT)) AS DATETIME)
END
Except for the scalar function part of that, that's the second fastest method in SQL Server 2012. A faster method would be to just convert it to the DATE datatype (and possibly back again) and not use a function at all for "Date Only" conversions.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply