Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My professional journey so far.
Browse by Tags · View All
BRH 5
TSQL 5
#SQLSERVER 4
SQLSERVER 3
#TSQL 2
DateTime 1
#SQL SERVER 1
SQL Server 1
Performance 1

Archive · View All
April 2011 2
May 2011 1
September 2010 1
August 2010 1

Top 10 Questions on Datetime asked in Forums

Sep 30 2010 5:25PM by Ramireddy   

There are innumerable numbers of questions asked in different forums about datetime concepts. I found so many replies using the functions such as Cast, Convert, Month () and Year () in where clause. There is a disadvantage with these functions. If the datetime column has index, these functions will not utilize the indexes properly, which often results in index scans. We can avoid these scans and replace with seeks, if the queries are properly written. Some queries will use functions such as DateName, DatePart etc, which will depends on regional settings and DateFirst value.

In general, by using calendar table, we can solve these questions very simply. However, if we don’t have such table in database, it will become quite difficult in some queries. Here I am presenting the top 10 questions, which will be asked very frequently and the solutions for those questions using Calendar table and without using Calendar table. Before using any of these queries, which uses the calendar table, please run the below script initially in your database, which creates a calendar table.

CREATE TABLE auxcalendardates
  (
     caldate DATETIME
  );

WITH n
     AS (SELECT 0 AS num
         UNION ALL
         SELECT 1
         UNION ALL
         SELECT 2
         UNION ALL
         SELECT 3
         UNION ALL
         SELECT 4
         UNION ALL
         SELECT 5
         UNION ALL
         SELECT 6
         UNION ALL
         SELECT 7
         UNION ALL
         SELECT 8
         UNION ALL
         SELECT 9),
     numbers
     AS (SELECT Row_number() OVER (ORDER BY (SELECT NULL)) AS NUMBER
         FROM   n n,
                n n1,
                n n2,
                n n3,
                n n4,
                n n5,
                n n6)
INSERT INTO auxcalendardates
SELECT Dateadd(DAY, NUMBER - 1, '1/1/1753')
FROM   numbers
WHERE  NUMBER <= 3012154

Here are the top 10 questions

  1. First date of Current Month
  2. Nth Weekday in current Month
  3. 1st Business day of Current Month
  4. Next N Business days
  5. Constructing a date with given Year,Month and day
  6. Year's first and Last dates.
  7. Given Year and Month's First and Last Dates
  8. Next weekday from today
  9. Stripping the Time portion
  10. Working days between 2 dates

1. First date of Current Month

select DATEADD(MOnth,datediff(Month,0,getdate()),0)

2. Nth Week day in Current Month
This will return the nth weekday of the current Month. This will takes 2 parameters, Week number and Week Day. Week day will take 0-6 as input values (0 – Monday, 1 – Tuesday, 2 – Wednesday, 3- Thursday, 4-Friday, 5- Saturday and 6- Sunday).

DECLARE @N INT = 1
DECLARE @Weekday INT = 0

--Without using Calendar Table	
SELECT Dateadd(DAY, ( 7 + @Weekday - Datediff(dd, 0, nthweekbegin)%7 ) % 7,
              nthweekbegin)
FROM  (SELECT Dateadd(DAY, ( @N - 1 ) * 7, Dateadd(MONTH, Datediff(MONTH, 0,
                                                          Getdate()
                                                                ), 0))
              AS nthweekbegin)t

--Using Calendar Table
;WITH cte
     AS (SELECT caldate,
                Datediff(dd, 0, caldate) % 7
                   AS wkday,
                Row_number() OVER (PARTITION BY Datediff(dd, 0, caldate)%7 ORDER
                BY
                   caldate) AS
                rn
         FROM   auxcalendardates
         WHERE  caldate >= Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0)
                AND caldate < Dateadd(MONTH, Datediff(MONTH, 0, Getdate()) + 1,
                              0))
SELECT caldate
FROM   cte
WHERE  rn = @N
       AND wkday = @Weekday

3. 1st Business day of Current Month
This will return the first business day, i.e. (Which is not Saturday and Sunday) of the current month.

--Without using Calendar Table 
SELECT Dateadd(DAY, CASE
                      WHEN ( Datediff(dd, 0, firstday)%7 ) IN ( 5, 6 ) THEN 7 -
                      Datediff(dd, 0, firstday)%7
                      ELSE 0
                    END, firstday)
FROM   (SELECT Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0) AS firstday)t

--Another way without using Calendar Table
SELECT Dateadd(DAY, CAST(Substring('0000021', ( Datediff(dd, 0, firstday)%7 ) +
                                              1, 1)
                                AS INT), firstday)
FROM   (SELECT Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0) AS firstday)t

--With using Calendar Table
SELECT TOP 1 caldate
FROM   auxcalendardates
WHERE  caldate >= Dateadd(MONTH, Datediff(MONTH, 0, Getdate()), 0)
       AND Datediff(dd, 0, caldate) % 7 <= 4
ORDER  BY caldate 

4. Next N Business days
This will return the Next N Business days, i.e. (Which are not Saturdays and Sundays) from today.

DECLARE @NoofWorkingDays INT = 7;

WITH n
     AS (SELECT 0 AS num
         UNION ALL
         SELECT 1
         UNION ALL
         SELECT 2
         UNION ALL
         SELECT 3
         UNION ALL
         SELECT 4
         UNION ALL
         SELECT 5
         UNION ALL
         SELECT 6
         UNION ALL
         SELECT 7
         UNION ALL
         SELECT 8
         UNION ALL
         SELECT 9),
     numbers
     AS (SELECT Row_number() OVER (ORDER BY (SELECT 1)) AS num
         FROM   n n1,
                n n2,
                n n3,
                n n4)
SELECT TOP (@NoofWorkingDays) Dateadd(DAY, num, Datediff(dd, 0, Getdate()))
FROM   numbers
WHERE  ( Datediff(dd, 0, Getdate()) + num )% 7 <= 4

--By using Calendar Table  
SELECT TOP (@NoofWorkingDays) caldate
FROM   auxcalendardates
WHERE  caldate > Datediff(dd, 0, Getdate())
       AND Datediff(dd, 0, caldate) % 7 <= 4
ORDER  BY caldate

5. Constructing a Date with given Year, Month and Day
This will take 3 parameters, Day, Month and Year and will return the datetime.

DECLARE @Day INT = 23
DECLARE @Month INT = 12
DECLARE @Year INT = 2010

SELECT Dateadd(DAY, @Day - 1, Dateadd(MONTH, @Month - 1 + ( @Year * 12 - 22800 )
                              , 0))

6. Given Year’s First and Last Dates
This will take the Year as input and gives the First and Last Dates of that year as output.

DECLARE @cYear INT = 2005

SELECT Dateadd(YEAR, @cYear - 1900, 0)     AS firstdayofyear,
       Dateadd(YEAR, @cYear - 1899, 0) - 1 AS lastdayofyear 

7. Given Year and Month’s First and Last Dates
This will take the Year and month as input and gives the First and last dates of the specified year and month as output.

DECLARE @cYear INT = 2005
DECLARE @cMonth INT = 2

SELECT Dateadd(MONTH, @cMonth - 1 + ( @cYear * 12 - 22800 ), 0) AS
       firstdayofyearmonth,
       Dateadd(MONTH, @cMonth + ( @cYear * 12 - 22800 ), 0) - 1 AS
       lastdayofyearmonth

8. Next weekday from today
This will return the next specified weekday from the current date. This will take Weekday as a parameter, Values to pass for week day are 0 – Monday, 1 – Tuesday, 2 – Wednesday, 3- Thursday, 4-Friday, 5- Saturday and 6- Sunday.

DECLARE @Wkday INT = 0

SELECT CASE
         WHEN @Wkday = Datediff(dd, 0, Getdate()) % 7 THEN
         Datediff(dd, 0, Getdate()) + 7
         ELSE Dateadd(DAY, ( 7 + @Wkday - ( Datediff(dd, 0, Getdate()) % 7 ) )%7
              ,
                     Datediff(dd, 0, Getdate()))
       END

--By using Calendar Table
SELECT TOP 1 caldate
FROM   auxcalendardates
WHERE  caldate > Datediff(dd, 0, Getdate())
       AND Datediff(dd, 0, caldate) % 7 = @Wkday

9. Stripping the time portion
This will strips the time portion of the specified datetime

SELECT Dateadd(DAY, Datediff(dd, 0, Getdate()), 0) 

10. Working days between 2 dates
This will provides the Working dates(Excluding Saturday and Sunday) between 2 specified dates.

DECLARE @StartDate DATETIME = '1/1/2001'
DECLARE @EndDate DATETIME = '1/31/2001'
--Without using calendar table
;WITH cte
     AS (SELECT @StartDate AS stdate
         UNION ALL
         SELECT stdate + 1
         FROM   cte
         WHERE  stdate < @EndDate)
SELECT *
FROM   cte
WHERE  Datediff(dd, 0, stdate)%7 BETWEEN 0 AND 4

--By using calendar Table
SELECT *
FROM   auxcalendardates
WHERE  caldate BETWEEN @startdate AND @enddate
       AND Datediff(dd, 0, caldate)%7 BETWEEN 0 AND 4 

About the author

RamiReddy

RamiReddy is a .NET Developer, who has a passtion towards t-sql. He has been working with .net/sql server for the Last 4 years. He spends his spare time helping people in the ASP.NET and SQL Server Forums. See Ramireddy's profile.

Tags: TSQL, BRH, SQL Server, #TSQL, #SQL SERVER, DateTime,


Ramireddy
2 · 40% · 12972
1
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

1  Comments  

  • Great Post Sir, Thank you since long back i was looking for this article.

    commented on Sep 26 2012 12:44AM
    santhosha.hp
    2855 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"Top 10 Questions on Datetime asked in Forums" rated 5 out of 5 by 1 readers
Top 10 Questions on Datetime asked in Forums , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]