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 - Create New Project

You can start building a new ASP.NET Core Application from the  File → New Project  menu option. On the New Project dialog box, you will see the following three different templates for Web projects − ASP.NET Web Application  − The simple ASP.NET application templates . ASP.NET Core Web Application (.NET Core)  − This will start you with a crossplatform compatible project that runs on the .NET Core framework. ASP.NET Core Web Application (.NET Framework)  − This starts a new project that runs on the standard .NET Framework on Windows. In the left pane, select  Templates → Visual C# → Web  and in the middle pane select the ASP.NET Core Web Application (.NET Core) template. Let us call this application  FirstAppDemo  and also specify the Location for your ASP.NET Core project and then Click OK. In the above dialog box, you can select a specific template for the ASP.NET application from the available ASP.NET Core Templates. ...
Post a Comment
Read more

ASP.NET Core - Attribute Routes

Learn another approach to routing and that is attribute-based routing. With attribute-based routing, we can use C# attributes on our controller classes and on the methods internally in these classes. These attributes have metadata that tell ASP.NET Core when to call a specific controller. It is an alternative to convention-based routing. Routes are evaluated in the order that they appear, the order that you register them in, but it's quite common to map multiple routes particularly if you want to have different parameters in the URL or if you want to have different literals in the URL. Example Let us take a simple example. Open the  FirstAppDemo  project and run the application in the browser. When you specify  /about , it will produce the following output − What we want here is that when we specify  /about , the application should invoke the Phone action of the AboutController. Here, we can enforce some explicit routes for this controller using a Ro...
Post a Comment
Read more

Features of Node.js

Following are some of the important features that make Node.js the first choice of software architects. Asynchronous and Event Driven  − All APIs of Node.js library are asynchronous, that is, non-blocking. It essentially means a Node.js based server never waits for an API to return data. The server moves to the next API after calling it and a notification mechanism of Events of Node.js helps the server to get a response from the previous API call. Very Fast  − Being built on Google Chrome's V8 JavaScript Engine, Node.js library is very fast in code execution. Single Threaded but Highly Scalable  − Node.js uses a single threaded model with event looping. Event mechanism helps the server to respond in a non-blocking way and makes the server highly scalable as opposed to traditional servers which create limited threads to handle requests. Node.js uses a single threaded program and the same program can provide service to a much larger number of requests than t...
Post a Comment
Read more