Skip to main content

SQL Server Tutorial - Date functions

1. Get month names with month numbers:

In this query we will learn about How to get all month names with month number in SQL Server. I have used this query to bind my dropdownlist with month name and month number.
1
2
3
4
5
6
7
8
9
10
11
12
;WITH months(MonthNumber) AS
(
    SELECT 0
    UNION ALL
    SELECT MonthNumber+1
    FROM months
    WHERE MonthNumber < 12
)
SELECT DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS [MonthName],Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS MonthNumber
FROM months
ORDER BY Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE()))
;

2. Get name of current month:

In this query we will learn about How to get name of current month in SQL Server.
1
select DATENAME(MONTH, GETDATE()) AS CurrentMonth

3. Get name of day:

In this query we will learn about How to get name of day in SQL Server.
1
select DATENAME(WEEKDAY, GETDATE()) AS TodayIs

4. Get first date of current month:

In this query we will learn about How to first date of current month in SQL Server.
1
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) FirstDate;

5. Get last date of current month:

In this query we will learn about How to last date of current month in SQL Server.
1
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) LastDate;

6. Get first date of previous month:

In this query we will learn about How to first date of previous month in SQL Server.
1
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) FirstDayOfPreviousMonth

7. Get last date of previous month:

In this query we will learn about How to last date of previous month in SQL Server.
1
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) LastDayOfPreviousMonth 

8. Get first date of current year:

In this query we will learn about How to first date of current year in SQL Server.
1
SELECT dateadd(year,datediff(year,0,getdate()),0) FirstDateOfYear

9. Get last date of current year:

In this query we will learn about How to last date of current year in SQL Server.
1
SELECT dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))) LasteDateOfYear 

10. Select date without time:

In this query we will learn about How to select date without time in SQL Server.
1
SELECT CONVERT(date, getdate()) AS DateWithoutTime 

11. Select records within two dates:

In this query we will learn about How to select records within two dates in SQL Server.
1
SELECT * FROM EMPLOYEE  WHERE CONVERT(date, CreatedOn) BETWEEN '1/1/2015' AND '12/31/2016'
Note: Here Employee is my table name and CreatedOn is a name of date column used for filtering.

12. Calculate age from birth date:

In this query we will learn about How to calculate age from birth date in SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '05/05/1989'
 
SELECT @tmpdate = @date
 
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
 
SELECT @years AS Years, @months AS Months, @days AS Days

13. Get days between two dates:

In this query we will learn about How to get days between two dates in SQL Server.
1
SELECT DATEDIFF(DAY, '1/1/2016', '3/1/2016') DaysDifference 

Labels:

Comments

Post a Comment

Popular posts from this blog

ASP.NET Core - MVC Design Pattern

The MVC (Model-View-Controller) design pattern is a design pattern that's actually been around for a few decades, and it's been used across many different technologies, everything from Smalltalk to C++ to Java and now in C# and .NET as a design pattern to use when you're building a user interface. The MVC design pattern is a popular design pattern for the user interface layer of a software application. In larger applications, you typically combine a model-view-controller UI layer with other design patterns in the application, like data access patterns and messaging patterns. These will all go together to build the full application stack. The MVC separates the user interface (UI) of an application into the following three parts − The Model  − A set of classes that describes the data you are working with as well as the business logic. The View  − Defines how the application’s UI will be displayed. It is a pure HTML which decides how the UI is going to loo...
Post a Comment
Read more
In a cloud computing system, there's a significant workload shift. Local computers no longer have to do all the heavy lifting when it comes to running applications. The network of computers that make up the cloud handles them instead. Hardware and software demands on the user's side decrease. The only thing the user's computer needs to be able to run is the cloud computing system's   interface software , which can be as simple as a Web browser, and the cloud's network takes care of the rest.
1 comment
Read more

Angular - Tutorial Part - 1 Application shell "Tour of languages"

The Application Shell Install the Angular CLI I nstall the  Angular CLI , if you haven't already done so. npm install - g @angular / cli Create a new application Create a new project named  angular-tour-of-languages  with this CLI command. ng new angular - tour - of -languages The Angular CLI generated a new project with a default application and supporting files. S erve the application Go to the project directory and launch the application. cd angular - tour - of - heroes ng serve -- open The   ng serve   command builds the app, starts the development server, watches the source files, and rebuilds the app as you make changes to those files. The   --open   flag opens a browser to   http://localhost:4200/ . You should see the app running in your browser. A ngular components The page you see is the  application shell . The shell is controlled by an Angular component named  AppComponent . Component...
Post a Comment
Read more