Finding Specific Dates in SQL Server

I gathered some useful date functions around the net. This is will be helpful in finding specific dates. Here are some of them:

declare @BaseDate datetime

set @BaseDate='20120205 21:30:27.380'

SELECT
Today=isnull(@BaseDate,GETDATE()),

DayToday=datename(dw,isnull(@BaseDate,GETDATE())),

TodayMidnight=DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0),

TodayEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,+1,isnull(@BaseDate,GETDATE()))),0)),


Yesterday=DATEADD(d,-1,isnull(@BaseDate,GETDATE())),

YesterdayMidnight=DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,-1,isnull(@BaseDate,GETDATE()))),0),

YesterdayEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),


Tomorrow=DateAdd(dd,1,isnull(@BaseDate,GETDATE())),

TomorrowMidnight=DATEADD(dd,DATEDIFF(dd,0,DateAdd(dd,1,isnull(@BaseDate,GETDATE()))),0),

TomorrowEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,+1,DateAdd(dd,1,isnull(@BaseDate,GETDATE())))),0)),


FirstDayofWeek=dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofWeek=dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))),

FirstDayofLastWeek=dateadd(dd,-7,dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))),

LastDayofLastWeek=dateadd(dd,-7,dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)))),


FirstDayofMonth=DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0),

LastDayofMonth=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE()))+1,0))),

FirstDayofLastMonth=DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofLastMonth=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0))),


LastDayLastQuarter=DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())), 0)),

FirstDayThisQuarter=DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())), 0),

LastDayThisQuarter=DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) +1, 0)),

FirstdayNextQuarter=DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) + 1, 0),

LastDayNextQuarter= DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) +2, 0)),


FirstDayofYear=DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0),

LastDayofYear=DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE()))+1,0))),

FirstDayofLastYear=DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofLastYear=DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0)))

Here's the result set:

Today                   DayToday    TodayMidnight           TodayEndofDay           
----------------------- ----------- ----------------------- ----------------------- 
2012-02-05 21:30:27.380 Sunday      2012-02-05 00:00:00.000 2012-02-05 23:59:59.997 


Yesterday               YesterdayMidnight       YesterdayEndofDay       
----------------------- ----------------------- ----------------------- 
2012-02-04 21:30:27.380 2012-02-04 00:00:00.000 2012-02-04 23:59:59.997 


Tomorrow                TomorrowMidnight         TomorrowEndofDay        
----------------------- ----------------------- ----------------------- 
2012-02-06 21:30:27.380 2012-02-06 00:00:00.000 2012-02-06 23:59:59.997 


FirstDayofWeek          LastDayofWeek           
----------------------- ----------------------- 
2012-01-30 00:00:00.000 2012-02-05 23:59:59.997 


FirstDayofLastWeek      LastDayofLastWeek       
----------------------- ----------------------- 
2012-01-23 00:00:00.000 2012-01-29 23:59:59.997 


FirstDayofMonth         LastDayofMonth          
----------------------- ----------------------- 
2012-02-01 00:00:00.000 2012-02-29 23:59:59.997 


FirstDayofLastMonth     LastDayofLastMonth      
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-01-31 23:59:59.997 


FirstDayLastQuarter     LastDayLastQuarter
----------------------- -----------------------
2011-10-01 00:00:00.000 2011-12-31 00:00:00.000


FirstDayThisQuarter     LastDayThisQuarter
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-03-31 00:00:00.000


FirstdayNextQuarter     LastDayNextQuarter
----------------------- -----------------------
2012-04-01 00:00:00.000 2012-06-30 00:00:00.000


FirstDayofYear          LastDayofYear           
----------------------- ----------------------- 
2012-01-01 00:00:00.000 2012-12-31 23:59:59.997 


FirstDayofLastYear      LastDayofLastYear
----------------------- -----------------------
2011-01-01 00:00:00.000 2011-12-31 23:59:59.997

Remember, the variable needs to be of DateTime datatype, not SmallDateTime.

~~ CK

Convert Delimited String into Rows

Here's another way of converting a delimited string into a result set or rows. This technique is much more straightforward and does not involve xml processing. It saves memory, specially if this step needs to be executed inside a loop.

Say you have this string:

x
----------------------------------------------------------
one, two, three, four, five, six, seven, eight, nine, ten

Which needs to be converted into:

xRow
--------
one
two
three
four
five
six
seven
eight
nine
ten

Here's the code...

First,  all spaces in the string must be removed and an extra delimiter needs to be added in the end of the string.

declare @x as varchar(150)

set @x = 'one, two, three, four, five, six, seven, eight, nine, ten'

set @x = replace(@x, ' ','') + ','

select @x as x

Second, using CTE, the string can be parse using the delimiter to identify where to break...

;with ToTable
as
(
   select cast(substring(@x,1, charindex(',',@x)-1) as varchar(150)) as xRow, cast(substring(@x,charindex(',',@x)+1, 150) as varchar(150)) as xValue2
   union all
   select cast(substring(xValue2,1, charindex(',',xValue2)-1) as varchar(150)) as xValues,  cast(substring(xValue2,charindex(',',xValue2)+1, 150) as varchar(150)) as xValue2
   from ToTable where xValue2 <> ''
)
select xRow
from ToTable

Now that it's a result set, it can be used to JOIN with other tables. Consideration should always be done how big the tables are.

If ever there's a need to use variable values inside an IN clause, this technique is very useful. Check this out on how to do this through XML.

As always, comments are welcome...


~~ CK

Converting Bytes to KB, MB or GB

I was looking for some algorithm on how to convert Bytes to KB or MB or GB depending on the value. Most of the algorithm that I saw are using case statement to implement the breakdown as brackets.

Here's my own version. This will generate some random number that will be converted to KB, MB or GB. But this should help me in the future.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint) 
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = coalesce(nullif(ByteSize/power(cast(1024 as bigint),4),0),
                        nullif(ByteSize/power(cast(1024 as bigint),3),0),
                        nullif(ByteSize/power(cast(1024 as bigint),2),0),
                        nullif(ByteSize/power(cast(1024 as bigint),1),0), 
                        ByteSize
                        ),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)), 
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)), 
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)), 
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)), 
                   'Bytes'
                  )
from SizeInStorage

Here's a sample result set:

ByteSize             Formatted       Converted   Unit
-------------------- --------------- ----------- -----
926528091            926,528,091     883         MB 
105563351            105,563,351     100         MB 
1603808599           1,603,808,599   1           GB 
866882480            866,882,480     826         MB 
426406913            426,406,913     406         MB 

Update...I want to convert bytes with two decimal places.

;with Sorted
as
(
   select 1 as base
   union all
   select base = base + 1
   from Sorted
   where base < 20
), Randomized
as
(
   select top 5 base
   from Sorted
   order by newid()
), SizeInStorage
as
(
select
   ByteSize = cast(abs(checksum(newid())) as bigint)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,5)
from randomized
union all
select top 1
   ByteSize = substring(cast(abs(checksum(newid())) as varchar(10)),1,3)
from randomized
)
select
   ByteSize,
   Formatted = replace(convert(varchar(20), cast(ByteSize as money),1),'.00',''),
   Converted = convert(varchar(30), isnull(cast(ByteSize as money)/ cast(coalesce((nullif((ByteSize/power(cast(1024 as bigint),4)),0)/nullif((ByteSize/power(cast(1024 as bigint),4)),0)) * power(cast(1024 as bigint),4),
                        (nullif((ByteSize/power(cast(1024 as bigint),3)),0)/nullif((ByteSize/power(cast(1024 as bigint),3)),0)) * power(cast(1024 as bigint),3),
                        (nullif((ByteSize/power(cast(1024 as bigint),2)),0)/nullif((ByteSize/power(cast(1024 as bigint),2)),0)) * power(cast(1024 as bigint),2),
                        (nullif((ByteSize/power(cast(1024 as bigint),1)),0)/nullif((ByteSize/power(cast(1024 as bigint),1)),0)) * power(cast(1024 as bigint),1)) as money)                                               
                        ,
                        ByteSize * 1.00
                           ), 1),
   Unit = coalesce('TB' + space(nullif(ByteSize/power(cast(1024 as bigint),4),0)),
                   'GB' + space(nullif(ByteSize/power(cast(1024 as bigint),3),0)),
                   'MB' + space(nullif(ByteSize/power(cast(1024 as bigint),2),0)),
                   'KB' + space(nullif(ByteSize/power(cast(1024 as bigint),1),0)),
                   'Bytes'
                  )
from SizeInStorage
 

Here's the resultset:

ByteSize      Formatted         Converted   Unit
------------  ----------------- ----------- ----
1582690891    1,582,690,891     1.47         GB
786337767     786,337,767       749.91       MB
431093153     431,093,153       411.12       MB
1067677070    1,067,677,070     1,018.22     MB
626880896     626,880,896       597.84       MB
28760         28,760            28.09        KB
308           308               308.00       Bytes

Comment?


~~ CK