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/