-- select * from [dbo].[GenDateTimes]( 'year', '1/1/0001', '12/12/9999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'month', '1/1/0001', '12/12/9999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'week', '1/1/0001', '12/12/9999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'day', '1/1/0001', '12/12/9999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'hour', '1/1/0001', '12/12/9999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'minute', '1/1/1999', '12/12/2999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'second', '1/1/1999', '12/12/1999' ) order by [Date]; -- select * from [dbo].[GenDateTimes]( 'millisecond', '1/1/1999', '1/12/1999' ) order by [Date]; create or alter function [dbo].[GenDateTimes]( @Increment varchar(11), @Start datetime2(3), @End datetime2(3) ) returns @Range table( [Date] datetime2(3) ) as begin; declare @units bigint = case when @Increment = 'ms' THEN datediff_big( ms, @Start, @End ) when @Increment = 'millisecond' THEN datediff_big( millisecond, @Start, @End ) when @Increment = 's' THEN datediff_big( s, @Start, @End ) when @Increment = 'second' THEN datediff_big( second, @Start, @End ) when @Increment = 'mi' THEN datediff_big( mi, @Start, @End ) when @Increment = 'minute' THEN datediff_big( minute, @Start, @End ) when @Increment = 'hh' THEN datediff_big( hh, @Start, @End ) when @Increment = 'hour' THEN datediff_big( hour, @Start, @End ) when @Increment = 'd' THEN datediff( d, @Start, @End ) when @Increment = 'day' THEN datediff( day, @Start, @End ) when @Increment = 'wk' THEN datediff( wk, @Start, @End ) when @Increment = 'week' THEN datediff( week, @Start, @End ) when @Increment = 'mm' THEN datediff( mm, @Start, @End ) when @Increment = 'month' THEN datediff( month, @Start, @End ) when @Increment = 'y' THEN datediff( y, @Start, @End ) when @Increment = 'year' THEN datediff( year, @Start, @End ) end; set @Increment = lower( @Increment ); with [l0] as ( select 0 [c] union all select 1 ), [l1] as ( select 1 [c] from [l0] [a] cross join [l0] [b] ), [l2] as ( select 1 [c] from [l1] [a] cross join [l1] [b] ), [l3] as ( select 1 [c] from [l2] [a] cross join [l2] [b] ), [l4] as ( select 1 [c] from [l3] [a] cross join [l3] [b] ), [l5] as ( select 1 [c] from [l4] [a] cross join [l4] [b] ), [nums] as ( select row_number() over( order by ( select null ) ) [n] from [l5] ), [cte]( [Date] ) AS ( select @Start [Date] union all select case when @Increment = 'ms' THEN dateadd( ms, [n], @Start ) when @Increment = 'millisecond' THEN dateadd( millisecond, [n], @Start ) when @Increment = 's' THEN dateadd( s, [n], @Start ) when @Increment = 'second' THEN dateadd( second, [n], @Start ) when @Increment = 'mi' THEN dateadd( mi, [n], @Start ) when @Increment = 'minute' THEN dateadd( minute, [n], @Start ) when @Increment = 'hh' THEN dateadd( hh, [n], @Start ) when @Increment = 'hour' THEN dateadd( hour, [n], @Start ) when @Increment = 'd' THEN dateadd( d, [n], @Start ) when @Increment = 'day' THEN dateadd( day, [n], @Start ) when @Increment = 'wk' THEN dateadd( wk, [n], @Start ) when @Increment = 'week' THEN dateadd( week, [n], @Start ) when @Increment = 'mm' THEN dateadd( mm, [n], @Start ) when @Increment = 'month' THEN dateadd( month, [n], @Start ) when @Increment = 'y' THEN dateadd( y, [n], @Start ) when @Increment = 'year' THEN dateadd( year, [n], @Start ) end from [nums] [t] where [t].[n] <= @units ) insert into @Range( [Date] ) select [Date] from [cte]; return; end;
-- select * from [dbo].[GenDates2]( 'd', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates2]( 'w', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates2]( 'm', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates2]( 'y', getdate()-1, getdate()-16384 ); create or alter function [dbo].[GenDates2]( @Increment char(1), @Start datetime2(3), @End datetime2(3) ) returns @Range table( [Date] datetime2(3), [Count] int ) as begin; set @Increment = lower( @Increment ); with cte( [Date], [Count] ) AS ( select @Start [Date], 0 [Count] union all select case when @Increment = 'd' THEN dateadd(day, 1, [Date]) when @Increment = 'w' THEN dateadd(week, 1, [Date]) when @Increment = 'm' THEN dateadd(month, 1, [Date]) when @Increment = 'y' THEN dateadd(year, 1, [Date]) end, case when @Increment = 'd' THEN datediff( day, @Start, dateadd(day, 1, [Date])) when @Increment = 'w' THEN datediff(week, @Start, dateadd(week, 1, [Date])) when @Increment = 'm' THEN datediff(month, @Start, dateadd(month, 1, [Date])) when @Increment = 'y' THEN datediff(year, @Start, dateadd(year, 1, [Date])) end [Count] from [cte] where [Date] <= case when @Increment = 'd' THEN dateadd(day, -1, @End) when @Increment = 'w' THEN dateadd(week, -1, @End) when @Increment = 'm' THEN dateadd(month, -1, @End) when @Increment = 'y' THEN dateadd(year, -1, @End) end ) insert into @Range( [Date], [Count] ) select [Date], [Count] from [cte] option (maxrecursion 32767); return; end;
SQL Function to Generate Date Table
Update: Please try the Improved version of this script.
-- select * from [dbo].[GenDates]( 'd', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates]( 'w', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates]( 'm', getdate()-16383, getdate()+16384 ); -- select * from [dbo].[GenDates]( 'y', getdate()-16383, getdate()+16384 ); create or alter function [dbo].[GenDates]( @Increment char(1), @Start datetime2(3), @End datetime2(3) ) returns @Range table( [Date] datetime2(3) ) as begin; set @Increment = lower( @Increment ); with [cte]( [Date] ) AS ( select @Start union all select case when @Increment = 'd' THEN dateadd(day, 1, [Date]) when @Increment = 'w' THEN dateadd(week, 1, [Date]) when @Increment = 'm' THEN dateadd(month, 1, [Date]) when @Increment = 'y' THEN dateadd(year, 1, [Date]) end from [cte] where [Date] <= case when @Increment = 'd' THEN dateadd(day, -1, @End) when @Increment = 'w' THEN dateadd(week, -1, @End) when @Increment = 'm' THEN dateadd(month, -1, @End) when @Increment = 'y' THEN dateadd(year, -1, @End) end ) insert into @Range( [Date] ) select [Date] from [cte] option (maxrecursion 32767); return; end;
Refuting Science?
What refutes science:
Better science.
What doesn’t refute science:
Your feelings.
Your religion.
Your favorite politician.
Your half-baked opinion after watching two YouTube videos.
Encapsulation – The process of hiding internal data away from other classes.
Abstraction – The process of hiding irrelevant details while providing a feature.
(with the side effect of reducing the code complexity.)
Inheritance – The process of providing properties and methods to derived classes.
Polymorphism – The ability of a derived class to take on different behaviors than the parent class provides.
Principles
I’ll take a person with principles and nothing else over someone with everything else any day.
Protiguous, 2020
RoboCopy – Exclude existing files.cmd
robocopy.exe d:Sourcepath e:Destpath /E /XC /XN /XO
The command line switches: /E makes Robocopy recursively copy subdirectories, including empty ones. /XC excludes existing files with the same timestamp, but different file sizes. Robocopy normally overwrites those. /XN excludes existing files newer than the copy in the source directory. Robocopy normally overwrites those. /XO excludes existing files older than the copy in the source directory. Robocopy normally overwrites those. With the Changed, Older, and Newer classes excluded, Robocopy will exclude files already existing in the destination directory.
Full-On Doc Brown Mode
My hair is about to go full Doc Brown..
Protiguous, 2020
I’m getting so old, that…
Chaotic Universe
Anyone who doesn’t believe in chaos has never tried to keep track of everything in the Universe.
Protiguous, 2020
Odd
You know what is odd?
Protiguous, 2020
The word, “even”..