Gauttam Jada: 2014

Wednesday 10 December 2014

Understanding ASP.NET MVC Filters and Attributes

ASP.NET MVC provides a simple way to inject your piece of code or logic either before or after an action is executed. This is achieved by decorating the controllers or actions with ASP.NET MVC attributes or custom attributes. An attribute or custom attribute implements the ASP.NET MVC filters(filter interface) and can contain your piece of code or logic. You can make your own custom filters or attributes either by implementing ASP.NET MVC filter interface or by inheriting and overriding methods of ASP.NET MVC filter attribute class if available.
Typically, Filters are used to perform the following common functionalities in your ASP.NET MVC application.

Custom Authentication
Custom Authorization(User based or Role based)
Error handling or logging
User Activity Logging
Data Caching
Data Compression

Types of Filters

The ASP.NET MVC framework provides five types of filters.

Authentication filters (New in ASP.NET MVC5)
Authorization filters
Action filters
Result filters
Exception filters

Authentication Filters

This filter is introduced with ASP.NET MVC5. The IAuthenticationFilter interface is used to create CustomAuthentication filter. The definition of this interface is given below-


public interface IAuthenticationFilter
{
 void OnAuthentication(AuthenticationContext filterContext);
 
 void OnAuthenticationChallenge(AuthenticationChallengeContext filterContext);
}

You can create your CustomAuthentication filter attribute by implementing IAuthenticationFilter as shown below-


public class CustomAuthenticationAttribute : ActionFilterAttribute, IAuthenticationFilter
{
 public void OnAuthentication(AuthenticationContext filterContext)
 { 
 //Logic for authenticating a user
 }
 //Runs after the OnAuthentication method
 public void OnAuthenticationChallenge(AuthenticationChallengeContext filterContext)
 { 
 //TODO: Additional tasks on the request
 }
}

Authorization Filters

The ASP.NET MVC Authorize filter attribute implements the IAuthorizationFilter interface. The definition of this interface is given below-


public interface IAuthorizationFilter
{
 void OnAuthorization(AuthorizationContext filterContext);
}

The AuthorizeAttribute class provides the following methods to override in the CustomAuthorize attribute class.


public class AuthorizeAttribute : FilterAttribute, IAuthorizationFilter
{
 protected virtual bool AuthorizeCore(HttpContextBase httpContext);
 protected virtual void HandleUnauthorizedRequest(AuthorizationContext filterContext);
 public virtual void OnAuthorization(AuthorizationContext filterContext);
 protected virtual HttpValidationStatus OnCacheAuthorization(HttpContextBase httpContext);
}

In this way you can make your CustomAuthorize filter attribute either by implementing IAuthorizationFilter interface or by inheriting and overriding above methods of AuthorizeAttribute class.

Action Filters

Action filters are executed before or after an action is executed. The IActionFilter interface is used to create an Action Filter which provides two methods OnActionExecuting and OnActionExecuted which will be executed before or after an action is executed respectively.


public interface IActionFilter
{
 void OnActionExecuting(ActionExecutingContext filterContext);
 void OnActionExecuted(ActionExecutedContext filterContext);
}

Result Filters

Result filters are executed before or after generating the result for an action. The Action Result type can be ViewResult, PartialViewResult, RedirectToRouteResult, RedirectResult, ContentResult, JsonResult, FileResult and EmptyResult which derives from the ActionResult class. Result filters are called after the Action filters. The IResultFilter interface is used to create an Result Filter which provides two methods OnResultExecuting and OnResultExecuted which will be executed before or after generating the result for an action respectively.


public interface IResultFilter
{
 void OnResultExecuted(ResultExecutedContext filterContext);
 void OnResultExecuting(ResultExecutingContext filterContext);
}

Exception Filters

Exception filters are executed when exception occurs during the actions execution or filters execution. The IExceptionFilter interface is used to create an Exception Filter which provides OnException method which will be executed when exception occurs during the actions execution or filters execution.


public interface IExceptionFilter
{
 void OnException(ExceptionContext filterContext);
}

ASP.NET MVC HandleErrorAttribute filter is an Exception filter which implements IExceptionFilter. When HandleErrorAttribute filter receives the exception it returns an Error view located in the Views/Shared folder of your ASP.NET MVC application.

Order of Filter Execution

All ASP.NET MVC filter are executed in an order. The correct order of execution is given below:

Authentication filters
Authorization filters
Action filters
Result filters

Configuring Filters

You can configure your own custom filter into your application at following three levels:

Global level
By registering your filter into Application_Start event of Global.asax.cs file with the help of FilterConfig class.
```
protected void Application_Start()
{
 FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
}
```

Controller level

By putting your filter on the top of the controller name as shown below-


[Authorize(Roles="Admin")]
public class AdminController : Controller
{
 //
}

Action level

By putting your filter on the top of the action name as shown below-


public class UserController : Controller
{
 [Authorize(Users="User1,User2")]
 public ActionResult LinkLogin(string provider)
 {
 // TODO:
 return View();
 }
}

What do you think?

I hope you will enjoy the ASP.NET MVC filter while extending ASP.NET MVC framework. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Tuesday 18 November 2014

Twitter Auth login using dot .Net C#

Hello all,

Twitter Login using Auth in c#;

Step 1 : Create OAuthHelper.cs File use this below code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.IO;
using System.Text;
public class OAuthHelper
{
    public OAuthHelper()
    {
    }

    private static string oauth_consumer_key = "YOUR CONSUMER KEY";
    private static string oauth_consumer_secret = "YOUR CONSUMER SECRET";
    private static string callbackUrl = "YOUR CALL BACK URL";

    #region (Changable) Do Not Change It

    private static string REQUEST_TOKEN = "YOUR REQUEST TOKEN URL";
    private static string AUTHORIZE = "YOUR AUTHORIZE URL";
    private static string ACCESS_TOKEN = "YOUR ACCESS TOKEN URL";

    public enum httpMethod
    {
        POST, GET
    }
    public string oauth_request_token
    {
        get;
        set;
    }
    public string oauth_access_token
    {
        get;
        set;
    }
    public string oauth_access_token_secret
    {
        get;
        set;
    }
    public string user_id
    {
        get;
        set;
    }
    public string screen_name
    {
        get;
        set;
    }
    public string oauth_error
    {
        get;
        set;
    }

    public string GetRequestToken()
    {
        HttpWebRequest request = FetchRequestToken(httpMethod.POST, oauth_consumer_key, oauth_consumer_secret);
        string result = getResponce(request);
        Dictionary<string, string> resultData = OAuthUtility.GetQueryParameters(result);
        if (resultData.Keys.Contains("oauth_token"))
            return resultData["oauth_token"];
        else
        {
            this.oauth_error = result;
            return "";
        }
    }
    public string GetAuthorizeUrl(string requestToken)
    {
        return string.Format("{0}?oauth_token={1}", AUTHORIZE, requestToken);
    }
    public void GetUserTwAccessToken(string oauth_token, string oauth_verifier)
    {
        HttpWebRequest request = FetchAccessToken(httpMethod.POST, oauth_consumer_key, oauth_consumer_secret, oauth_token, oauth_verifier);
        string result = getResponce(request);

        Dictionary<string, string> resultData = OAuthUtility.GetQueryParameters(result);
        if (resultData.Keys.Contains("oauth_token"))
        {
            this.oauth_access_token = resultData["oauth_token"];
            this.oauth_access_token_secret = resultData["oauth_token_secret"];
            this.user_id = resultData["user_id"];
            this.screen_name = resultData["screen_name"];
        }
        else
            this.oauth_error = result;
    }
    public void TweetOnBehalfOf(string oauth_access_token, string oauth_token_secret, string postData)
    {
        HttpWebRequest request = PostTwits(oauth_consumer_key, oauth_consumer_secret, oauth_access_token, oauth_token_secret, postData);
        string result = OAuthHelper.getResponce(request);
        Dictionary<string, string> dcResult = OAuthUtility.GetQueryParameters(result);
        if (dcResult["status"] != "200")
        {
            this.oauth_error = result;
        }

    }

    HttpWebRequest FetchRequestToken(httpMethod method, string oauth_consumer_key, string oauth_consumer_secret)
    {
        string OutUrl = "";
        string OAuthHeader = OAuthUtility.GetAuthorizationHeaderForPost_OR_QueryParameterForGET(new Uri(REQUEST_TOKEN), callbackUrl, method.ToString(), oauth_consumer_key, oauth_consumer_secret, "", "", out OutUrl);

        if (method == httpMethod.GET)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(OutUrl + "?" + OAuthHeader);
            request.Method = method.ToString();
            return request;
        }
        else if (method == httpMethod.POST)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(OutUrl);
            request.Method = method.ToString();
            request.Headers["Authorization"] = OAuthHeader;
            return request;
        }
        else
            return null;

    }
    HttpWebRequest FetchAccessToken(httpMethod method, string oauth_consumer_key, string oauth_consumer_secret, string oauth_token, string oauth_verifier)
    {
        string postData = "oauth_verifier=" + oauth_verifier;
        string AccessTokenURL = string.Format("{0}?{1}", ACCESS_TOKEN, postData);
        string OAuthHeader = OAuthUtility.GetAuthorizationHeaderForPost_OR_QueryParameterForGET(new Uri(AccessTokenURL), callbackUrl, method.ToString(), oauth_consumer_key, oauth_consumer_secret, oauth_token, "", out AccessTokenURL);

        if (method == httpMethod.GET)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(AccessTokenURL + "?" + OAuthHeader);
            request.Method = method.ToString();
            return request;
        }
        else if (method == httpMethod.POST)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(AccessTokenURL);
            request.Method = method.ToString();
            request.Headers["Authorization"] = OAuthHeader;

            byte[] array = Encoding.ASCII.GetBytes(postData);
            request.GetRequestStream().Write(array, 0, array.Length);
            return request;
        }
        else
            return null;

    }
    HttpWebRequest PostTwits(string oauth_consumer_key, string oauth_consumer_secret, string oauth_access_token, string oauth_token_secret, string postData)
    {
        postData = "trim_user=true&include_entities=true&status=" + postData;
        string updateStatusURL = "https://api.twitter.com/1/statuses/update.json?" + postData;

        string outUrl;
        string OAuthHeaderPOST = OAuthUtility.GetAuthorizationHeaderForPost_OR_QueryParameterForGET(new Uri(updateStatusURL), callbackUrl, httpMethod.POST.ToString(), oauth_consumer_key, oauth_consumer_secret, oauth_access_token, oauth_token_secret, out outUrl);

        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(outUrl);
        request.Method = httpMethod.POST.ToString();
        request.Headers["Authorization"] = OAuthHeaderPOST;

        byte[] array = Encoding.ASCII.GetBytes(postData);
        request.GetRequestStream().Write(array, 0, array.Length);
        return request;

    }

    public static string getResponce(HttpWebRequest request)
    {
        try
        {
            HttpWebResponse resp = (HttpWebResponse)request.GetResponse();
            StreamReader reader = new StreamReader(resp.GetResponseStream());
            string result = reader.ReadToEnd();
            reader.Close();
            return result + "&status=200";
        }
        catch (Exception ex)
        {
            string statusCode = "";
            if (ex.Message.Contains("403"))
                statusCode = "403";
            else if (ex.Message.Contains("401"))
                statusCode = "401";
            return string.Format("status={0}&error={1}", statusCode, ex.Message);
        }
    }

    #endregion
}

Step 2 : CreateOAuthUtility.cs File use this below code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;

public class OAuthUtility
{
    public OAuthUtility()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    #region *******Common Methods**********
    protected static string unreservedChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-_.~";
    public static string UrlEncode(string value)
    {
        StringBuilder result = new StringBuilder();

        foreach (char symbol in value)
        {
            if (unreservedChars.IndexOf(symbol) != -1)
            {
                result.Append(symbol);
            }
            else
            {
                result.Append('%' + String.Format("{0:X2}", (int)symbol));
            }
        }

        return result.ToString();
    }

    public static string GenerateTimeStamp()
    {
        TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
        return Convert.ToInt64(ts.TotalSeconds).ToString();
    }
    public static string GenerateNonce()
    {
        // Just a simple implementation of a random number between 123400 and 9999999
        Random random = new Random();
        return random.Next(123400, 9999999).ToString();
    }
    public static Dictionary<string, string> GetQueryParameters(string dataWithQuery)
    {
        Dictionary<string, string> result = new Dictionary<string, string>();
        string[] parts = dataWithQuery.Split('?');
        if (parts.Length > 0)
        {
            string QueryParameter = parts.Length > 1 ? parts[1] : parts[0];
            if (!string.IsNullOrEmpty(QueryParameter))
            {
                string[] p = QueryParameter.Split('&');
                foreach (string s in p)
                {
                    if (s.IndexOf('=') > -1)
                    {
                        string[] temp = s.Split('=');
                        result.Add(temp[0], temp[1]);
                    }
                    else
                    {
                        result.Add(s, string.Empty);
                    }
                }
            }
        }
        return result;
    }
    #endregion Common Methods

    public static string GetAuthorizationHeaderForPost_OR_QueryParameterForGET(Uri url, string callbackUrl, string httpMethod, string consumerKey, string consumerSecret, string token, string tokenSecret, out string normalizedUrl)
    {
        string normalizedParameters = "";

        Dictionary<string, string> parameters = new Dictionary<string, string>();
        parameters.Add("oauth_version", "1.0");
        if (token != "")
            parameters.Add("oauth_token", token);
        parameters.Add("oauth_nonce", GenerateNonce()); //Random String
        parameters.Add("oauth_timestamp", GenerateTimeStamp()); // Current Time Span
        parameters.Add("oauth_consumer_key", consumerKey); //Customer Consumer Key
        parameters.Add("oauth_signature_method", "HMAC-SHA1"); //Singnatur Encription Method
        parameters.Add("oauth_callback", UrlEncode(callbackUrl)); //return url

        Dictionary<string, string> drQuery = GetQueryParameters(url.Query);
        foreach (string key in drQuery.Keys)
            parameters.Add(key, drQuery[key]);

        if (url.Query != "")
            normalizedUrl = url.AbsoluteUri.Replace(url.Query, "");
        else
            normalizedUrl = url.AbsoluteUri;

        List<string> li = parameters.Keys.ToList();
        li.Sort();

        StringBuilder sbOAuthHeader = new StringBuilder("OAuth ");
        StringBuilder sbSignatureBase = new StringBuilder();
        foreach (string k in li)
        {
            sbSignatureBase.AppendFormat("{0}={1}&", k, parameters[k]); // For Signature and Get Date (QueryString)
            sbOAuthHeader.AppendFormat("{0}=\"{1}\", ", k, parameters[k]); // For Post Request (Post Data)
        }

        string signature = GenerateSignatureBySignatureBase(httpMethod, consumerSecret, tokenSecret, normalizedUrl, sbSignatureBase);

        if (httpMethod == "POST")
        {
            string OAuthHeader = sbOAuthHeader.Append("oauth_signature=\"" + UrlEncode(signature) + "\"").ToString();
            normalizedParameters = OAuthHeader;
        }
        else if (httpMethod == "GET")
        {
            normalizedParameters = sbSignatureBase.AppendFormat("{0}={1}", "oauth_signature", signature).ToString(); ;
        }
        return normalizedParameters;
    }
    private static string GenerateSignatureBySignatureBase(string httpMethod, string consumerSecret, string tokenSecret, string normalizedUrl, StringBuilder sbSignatureBase)
    {
        string normalizedRequestParameters = sbSignatureBase.ToString().TrimEnd('&');
        StringBuilder signatureBase = new StringBuilder();
        signatureBase.AppendFormat("{0}&", httpMethod.ToString());
        signatureBase.AppendFormat("{0}&", UrlEncode(normalizedUrl));
        signatureBase.AppendFormat("{0}", UrlEncode(normalizedRequestParameters));

        HMACSHA1 hmacsha1 = new HMACSHA1();
        hmacsha1.Key = Encoding.ASCII.GetBytes(string.Format("{0}&{1}", UrlEncode(consumerSecret), UrlEncode(tokenSecret)));
        byte[] hashBytes = hmacsha1.ComputeHash(System.Text.Encoding.ASCII.GetBytes(signatureBase.ToString()));
        return Convert.ToBase64String(hashBytes);
    }
}

Step 3 : Create login.aspx page use this code:
var oauthhelper = new OAuthHelper();
            string requestToken = oauthhelper.GetRequestToken();

            if (string.IsNullOrEmpty(oauthhelper.oauth_error))
                Response.Redirect(oauthhelper.GetAuthorizeUrl(requestToken));
            else
                Response.Write(oauthhelper.oauth_error);

Step 4 : Create Response.aspx page to handle response use this code:
                   if (Request.QueryString["oauth_token"] != null && Request.QueryString["oauth_verifier"] != null)
                {
                    string oauth_token = Request.QueryString["oauth_token"];
                    string oauth_verifier = Request.QueryString["oauth_verifier"];
}

Do you still need any help? Feel free to comment

Sunday 16 November 2014

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.

-- Get date only from datetime - QUICK SYNTAX

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, CURRENT_TIMESTAMP)) -- 2016-10-23 00:00:00.000

------------
-- SQL Server T-SQL date & datetime formats - Gregorian calendar - Christian calendar
-- getdate() / CURRENT_TIMESTAMP(ANSI) returns system date & time in standard format
-- SQL datetime formats with century (YYYY or CCYY format)- sql date & time format
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) -- Oct 2 2010 11:01AM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

-- Oct 2 2010 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

-- yyyymmdd - ISO date format - international standard - works with any language setting

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

-- 02 Oct 2010 11:02:07:577

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm - ISO8601

-- 2010-10-02T10:52:47.513

SELECT convert(varchar, getdate(), 127) -- yyyy-mm-ddThh:mm:ss.mmmZ - with time zone

SELECT convert(nvarchar(64), getdate(), 130) -- Arabic Hijri date

-- 29 جمادى الاولى 1433 1:13:04:633AM:

SELECT convert(nvarchar, getdate(), 131) -- Arabic Hijri date - Islamic calendar

-- 29/05/1433 12:57:26:690AM

-- Without century (YY) date / datetime conversion - there are exceptions!

SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(varchar, getdate(), 1) -- mm/dd/yy

SELECT convert(varchar, getdate(), 2) -- yy.mm.dd

SELECT convert(varchar, getdate(), 3) -- dd/mm/yy

SELECT convert(varchar, getdate(), 4) -- dd.mm.yy

SELECT convert(varchar, getdate(), 5) -- dd-mm-yy

SELECT convert(varchar, getdate(), 6) -- dd mon yy

SELECT convert(varchar, getdate(), 7) -- mon dd, yy

SELECT convert(varchar, getdate(), 8) -- hh:mm:ss

SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 10) -- mm-dd-yy

SELECT convert(varchar, getdate(), 11) -- yy/mm/dd

SELECT convert(varchar, getdate(), 12) -- yymmdd

SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)

SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd

SELECT convert(varchar, getdate(), 24) -- hh:mm:ss

SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm

-- SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm
----------

-- Current (today) midnight calculation - remove time part of datetime

-- Today's date without time - datetime without time

DECLARE @CurrentMidnight datetime =

dateadd(day, datediff(day,0, CURRENT_TIMESTAMP), 0)

SELECT @CurrentMidnight

-- 2012-03-15 00:00:00.000

----------

------------

-- SQL Server date formatting function - convert datetime to string

------------

-- SQL datetime functions - SQL date functions - SQL server datetime formatting

-- T-SQL convert dates - T-SQL date formats - Transact-SQL date formats

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))

RETURNS VARCHAR(32)

BEGIN

DECLARE @StringDate VARCHAR(32)

SET @StringDate = @FormatMask

IF (CHARINDEX ('YYYY',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @Datetime))

IF (CHARINDEX ('YY',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @Datetime),2))

IF (CHARINDEX ('Month',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'Month', DATENAME(MM, @Datetime))

IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)

SET @StringDate = REPLACE(@StringDate, 'MON',

LEFT(UPPER(DATENAME(MM, @Datetime)),3))

IF (CHARINDEX ('Mon',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @Datetime),3))

IF (CHARINDEX ('MM',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'MM',

RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))

IF (CHARINDEX ('M',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'M',

CONVERT(VARCHAR,DATEPART(MM, @Datetime)))

IF (CHARINDEX ('DD',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'DD',

RIGHT('0'+DATENAME(DD, @Datetime),2))

IF (CHARINDEX ('D',@StringDate) > 0)

SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime))

RETURN @StringDate

END

-- Microsoft SQL Server date format function test - MSSQL formatting date - sql datetime

SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY') -- 01/03/2012

SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY') -- 03/01/2012

SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY') -- 1/03/2012

SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY') -- 1/3/2012

SELECT dbo.fnFormatDate (getdate(), 'M/D/YY') -- 1/3/12

SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY') -- 01/03/12

SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY') -- JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY') -- Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY') -- January 03, 2012

SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD') -- 2012/01/03

SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD') -- 20120103

SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD') -- 2012-01-03

-- CURRENT_TIMESTAMP returns current system date and time in standard internal format

SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD') -- 12.01.03
-- Convert date into integer format

DECLARE @date as DATETIME = CURRENT_TIMESTAMP;

SELECT CONVERT(int, CONVERT(char(8), @date, 112)) AS DateInt

-- 20120312

------------

/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

-- SQL format datetime - - sql hh mm ss - sql yyyy mm dd

-- Default format: Oct 23 2006 10:40AM

SELECT [Default]=CONVERT(varchar,GETDATE(),100)

-- US-Style format: 10/23/2006

SELECT [US-Style]=CONVERT(char,GETDATE(),101)

-- ANSI format: 2006.10.23

SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

-- UK-Style format: 23/10/2006

SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

-- German format: 23.10.2006

SELECT [German]=CONVERT(varchar,GETDATE(),104)

-- ISO format: 20061023

SELECT ISO=CONVERT(varchar,GETDATE(),112)

-- ISO8601 format: 2010-10-23T19:20:16.003

SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

------------

-- Islamic date conversion

------------

-- Gregorian date

SELECT CONVERT(VARCHAR(36), GETDATE(), 109)

-- Mar 17 2012 9:27:19:027PM

-- Islamic / Hijri date

SELECT CONVERT(NVARCHAR(36), GETDATE(), 130)

-- 24 ربيع الثاني 1433 9:27:19:030PM

SELECT CONVERT(VARCHAR(36), GETDATE(), 131)

-- 24/04/1433 9:27:19:030PM

------------

-- SQL Server datetime formats - Format dates SQL Server 2005 / 2008
-- Century date format MM/DD/YYYY usage in a query

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(char(10), OrderDate, 101),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

SalesOrderID OrderDate OrderDateTime

43697 07/01/2001 2001-07-01 00:00:00.000

-- SQL update datetime column - SQL datetime DATEADD - datetime function

UPDATE Production.Product

SET ModifiedDate=DATEADD(dd,1, ModifiedDate)

WHERE ProductID = 1001

-- MM/DD/YY date format - Datetime format sql

SELECT TOP (1)

SalesOrderID,

OrderDate = CONVERT(varchar(8), OrderDate, 1),

OrderDateTime = OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY SalesOrderID desc

SalesOrderID OrderDate OrderDateTime

75123 07/31/04 2004-07-31 00:00:00.000

------------

-- Converting UNIX timestamp to datetime

DECLARE @Date BIGINT = 1477180800

/* (UNIX time = 1477180800 --> midnight, OCT 23, 2016) */

SELECT DATEADD(ss, @Date, '19700101')

-- 2016-10-23 00:00:00.000

------------

-- SQL convert datetime to char - sql date string concatenation: + (plus) operator

PRINT 'Style 110: '+CONVERT(CHAR(10),GETDATE(),110) -- Style 110: 07-10-2012

PRINT 'Style 111: '+CONVERT(CHAR(10),GETDATE(),111) -- Style 111: 2012/07/10

PRINT 'Style 112: '+CONVERT(CHAR(8), GETDATE(),112) -- Style 112: 20120710

------------

-- Combining different style formats for date & time

-- Datetime formats - sql times format - datetime formats sql

DECLARE @Date DATETIME

SET @Date = '2015-12-22 03:51 PM'

SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)

-- Result: 12-22-2015 3:51PM

-- Microsoft SQL Server cast datetime to string

SELECT stringDateTime=CAST (getdate() as varchar)

-- Result: Dec 29 2012 3:47AM

Related article: Date and Time Functions (Transact-SQL)

------------

-- SQL Server date and time functions overview

------------

-- SQL Server CURRENT_TIMESTAMP function - SQL Server datetime functions

-- Local NYC - EST - Eastern Standard Time zone

-- SQL DATEADD function - SQL DATEDIFF function

SELECT CURRENT_TIMESTAMP -- 2012-01-05 07:02:10.577

-- SQL Server DATEADD function

SELECT DATEADD(month,2,'2012-12-09') -- 2013-02-09 00:00:00.000

-- SQL Server DATEDIFF function

SELECT DATEDIFF(day,'2012-12-09','2013-02-09') -- 62

-- SQL Server DATENAME function

SELECT DATENAME(month, '2012-12-09') -- December

SELECT DATENAME(weekday, '2012-12-09') -- Sunday

-- SQL Server DATEPART function

SELECT DATEPART(month, '2012-12-09') -- 12

-- SQL Server DAY function

SELECT DAY('2012-12-09') -- 9

-- SQL Server GETDATE function

-- local NYC - EST - Eastern Standard Time zone

SELECT GETDATE() -- 2012-01-05 07:02:10.577

-- SQL Server GETUTCDATE function

-- London - Greenwich Mean Time

SELECT GETUTCDATE() -- 2012-01-05 12:02:10.577

-- SQL Server MONTH function

SELECT MONTH('2012-12-09') -- 12

-- SQL Server YEAR function

SELECT YEAR('2012-12-09') -- 2012

------------

-- Universal CONVERT function - datetime conversion UDF

------------

CREATE FUNCTION fnCONVERT( @Input datetimeoffset, @StyleNo int)

RETURNS nvarchar(35) AS

BEGIN

RETURN (CONVERT(nvarchar(35), @Input, @StyleNo))

END

DECLARE @dt DATE=getdate(); SELECT dbo.fnCONVERT( @dt,106) -- 23 Dec 2010

DECLARE @dt DATE=getdate(); SELECT dbo.fnCONVERT( @dt,156) -- 23 Dec 2010

/* Msg 281, Level 16, State 1, Line 1

156 is not a valid style number when converting from datetimeoffset

to a character string. */

DECLARE @dt DATETIME=getdate(); SELECT dbo.fnCONVERT( @dt,112) -- 20101223

DECLARE @dt SMALLDATETIME=getdate(); SELECT dbo.fnCONVERT( @dt,101) -- 12/23/2010

------------

-- SQL calculate the number of business days function - exclude Saturdays & Sundays

------------

CREATE FUNCTION fnBusinessDaysCount (@StartDate DATE, @EndDate DATE)

RETURNS INT AS

BEGIN

IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)

DECLARE @i INT = 0;

WHILE (@StartDate <= @EndDate)

BEGIN

SET @i = @i + CASE

WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1

ELSE 0

END

SET @StartDate = DATEADD(dd,1,@StartDate)

END -- while

RETURN (@i)

END -- function

SELECT dbo.fnBusinessDaysCount('2016-01-01','2016-12-31')

-- 261

------------

-- T-SQL Date and time function application

-- CURRENT_TIMESTAMP and getdate() are the same in T-SQL

------------

-- T-SQL first day of week and last day of week

SELECT FirstDateOfWeek = dateadd(dd,-DATEPART(dw,GETDATE()) + 1,GETDATE())

SELECT LastDateOfWeek = dateadd(dd,7 - DATEPART(dw,GETDATE()),GETDATE())

-- SQL first day of the month - SQL first date of the month

-- SQL first day of current month - 2012-01-01 00:00:00.000

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

-- SQL last day of the month - SQL last date of the month

-- SQL last day of current month - 2012-01-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))

-- SQL first day of last month

-- SQL first day of previous month - 2011-12-01 00:00:00.000

SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

-- SQL last day of last month

-- SQL last day of previous month - 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))

-- SQL first day of next month - 2012-02-01 00:00:00.000

SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))

-- SQL last day of next month - 2012-02-28 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))

-- SQL first day of a month - 2012-10-01 00:00:00.000

DECLARE @Date datetime; SET @Date = '2012-10-23'

SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))

-- SQL last day of a month - 2012-03-31 00:00:00.000

DECLARE @Date datetime; SET @Date = '2012-03-15'

SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))
GO

-- SQL first day of year - SQL first day of the year - 2012-01-01 00:00:00.000

SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)

-- SQL last day of year - SQL last day of the year - 2012-12-31 00:00:00.000

SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,

DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))

-- SQL last day of last year - SQL last day of previous year - 2011-12-31 00:00:00.000

SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))
GO

------------

-- First and last day of date periods

DECLARE @Date DATETIME; SET = @Date = '20161023';

SELECT ReferenceDate = @Date

SELECT FirstDayOfYear = DATEADD(YY, DATEDIFF(YY,0, @Date),0)

SELECT LastDayOfYear = DATEADD(YY, DATEDIFF(YY,0, @Date)+1,-1)

SELECT FirstDayOfSemester = DATEADD(QQ,((DATEDIFF(QQ,0,@Date)/2)*2),0)

SELECT LastDayOfSemester = DATEADD(QQ,((DATEDIFF(QQ,0,@Date)/2)*2)+2,-1)

SELECT FirstDayOfQuarter = DATEADD(QQ, DATEDIFF(QQ,0, @Date),0)

-- 2016-10-01 00:00:00.000

SELECT LastDayOfQuarter = DATEADD(QQ, DATEDIFF(QQ,0, @Date)+1,-1)

-- 2016-12-31 00:00:00.000

SELECT FirstDayOfMonth = DATEADD(MM, DATEDIFF(MM,0, @Date),0)

SELECT LastDayOfMonth = DATEADD(MM, DATEDIFF(MM,0, @Date)+1,-1)

SELECT FirstDayOfWeek = DATEADD(WK, DATEDIFF(WK,0, @Date),0)

SELECT LastDayOfWeek = DATEADD(WK, DATEDIFF(WK,0, @Date)+1,-1)

-- 2016-10-30 00:00:00.000

------------

-- Start of week SUNDAY - US_english language setting -

SELECT CURRENT_TIMESTAMP, DATEADD (week, DATEDIFF(week,6, CURRENT_TIMESTAMP),6)

-- End of week SATURDAY

SELECT CURRENT_TIMESTAMP, DATEADD (week, DATEDIFF(week,5, CURRENT_TIMESTAMP),5)

------------

-- SQL calculate age in years, months, days - Format dates SQL Server 2008

-- SQL table-valued function - SQL user-defined function - UDF

-- SQL Server age calculation - date difference

USE AdventureWorks2008;

CREATE FUNCTION fnAge (@BirthDate DATETIME)

RETURNS @Age TABLE(Years INT,

Months INT,

Days INT)

BEGIN

DECLARE @EndDate DATETIME, @Anniversary DATETIME

SET @EndDate = Getdate()

SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)

INSERT @Age

SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE

WHEN @Anniversary > @EndDate THEN 1

ELSE 0

END), 0, 0

UPDATE @Age SET Months = Month(@EndDate - @Anniversary) - 1

UPDATE @Age SET Days = Day(@EndDate - @Anniversary) - 1

RETURN

END

-- Test table-valued UDF

SELECT * FROM fnAge('1956-10-23')

SELECT * FROM dbo.fnAge('1956-10-23')

/* Results

Years Months Days

52 4 1

----------

-- SQL date range between

----------

-- SQL between dates

USE AdventureWorks;

-- SQL between

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate BETWEEN '20040301' AND '20040315'

-- Result: 108

-- BETWEEN operator is equivalent to >=...AND....<=

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate

BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-15 00:00:00.000'

Orders with OrderDates

'2004-03-15 00:00:01.000' - 1 second after midnight (12:00AM)

'2004-03-15 00:01:00.000' - 1 minute after midnight

'2004-03-15 01:00:00.000' - 1 hour after midnight

are not included in the two queries above.

-- To include the entire day of 2004-03-15 use the following two solutions

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE OrderDate >= '20040301' AND OrderDate < '20040316'

-- SQL between with DATE type (SQL Server 2008)

SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader

WHERE CONVERT(DATE, OrderDate) BETWEEN '20040301' AND '20040315'

----------

-- Non-standard format conversion: 2011 December 14

-- SQL datetime to string

SELECT [YYYY Month DD] =

CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' '+

DATENAME(MM, GETDATE()) + ' ' +

CAST(DAY(GETDATE()) AS VARCHAR(2))

-- Converting datetime to YYYYMMDDHHMMSS format: 20121214172638

SELECT replace(convert(varchar, getdate(),111),'/','') +

replace(convert(varchar, getdate(),108),':','')

-- Datetime custom format conversion to YYYY_MM_DD

select CurrentDate=rtrim(year(getdate())) + '_' +

right('0' + rtrim(month(getdate())),2) + '_' +

right('0' + rtrim(day(getdate())),2)

-- Converting seconds to HH:MM:SS format

declare @Seconds int

set @Seconds = 10000

select TimeSpan=right('0' +rtrim(@Seconds / 3600),2) + ':' +

right('0' + rtrim((@Seconds % 3600) / 60),2) + ':' +

right('0' + rtrim(@Seconds % 60),2)

-- Result: 02:46:40

-- Test result

select 2*3600 + 46*60 + 40

-- Result: 10000

-- Set the time portion of a datetime value to 00:00:00.000

-- SQL strip time from date

-- SQL strip time from datetime

SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)

-- Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000

/* VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

SMALLDATETIME (4 bytes) date range:

January 1, 1900 through June 6, 2079

DATETIME (8 bytes) date range:

January 1, 1753 through December 31, 9999

DATETIME2 (8 bytes) date range (SQL Server 2008):

January 1,1 AD through December 31, 9999 AD

DATE (3 bytes) date range (SQL Server 2008):

January 1, 1 AD through December 31, 9999 AD

*******/

-- Selecting with CONVERT into different styles

-- Note: Only Japan & ISO styles can be used in ORDER BY

SELECT TOP(1)

Italy = CONVERT(varchar, OrderDate, 105)

, USA = CONVERT(varchar, OrderDate, 110)

, Japan = CONVERT(varchar, OrderDate, 111)

, ISO = CONVERT(varchar, OrderDate, 112)

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY PurchaseOrderID DESC

/* Results

Italy USA Japan ISO

25-07-2004 07-25-2004 2004/07/25 20040725

-- SQL Server convert date to integer

DECLARE @Datetime datetime

SET @Datetime = '2012-10-23 10:21:05.345'

SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)

-- Result: 20121023

-- SQL Server convert integer to datetime

DECLARE @intDate int

SET @intDate = 20120315

SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)

-- Result: 2012-03-15 00:00:00.000

-------------

-- Julian date (YYYYDDD) to date / datetime converter

------------

CREATE FUNCTION dbo.fnJulianToDate (@JulianDt char(7))

RETURNS date AS

BEGIN

RETURN (SELECT DATEADD(day, CAST(RIGHT(@JulianDt,3) AS int) - 1,

CONVERT(datetime, LEFT(@JulianDt,4) + '0101', 112)))

END

SELECT dbo.fnJulianToDate ('2016040')

-- 2016-02-09

------------

-----------

-- SQL Server CONVERT script applying table INSERT/UPDATE

------------

-- SQL Server convert date

-- Datetime column is converted into date only string column

USE tempdb;

CREATE TABLE sqlConvertDateTime (

DatetimeCol datetime,

DateCol char(8));

INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

UPDATE sqlConvertDateTime

SET DateCol = CONVERT(char(10), DatetimeCol, 112)

SELECT * FROM sqlConvertDateTime

-- SQL Server convert datetime

-- The string date column is converted into datetime column

UPDATE sqlConvertDateTime

SET DatetimeCol = CONVERT(Datetime, DateCol, 112)

SELECT * FROM sqlConvertDateTime

-- Adding a day to the converted datetime column with DATEADD

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))

SELECT * FROM sqlConvertDateTime

-- Equivalent formulation - SQL Server CAST datetime

UPDATE sqlConvertDateTime

SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))

SELECT * FROM sqlConvertDateTime

DROP TABLE sqlConvertDateTime

/* First results

DatetimeCol DateCol

2014-12-25 16:04:15.373 20141225 */

/* Second results:

DatetimeCol DateCol

2014-12-25 00:00:00.000 20141225 */

/* Third results:

DatetimeCol DateCol

2014-12-26 00:00:00.000 20141225 */

------------

-- SQL month sequence - SQL date sequence generation with table variable

-- SQL Server cast string to datetime - SQL Server cast datetime to string

-- SQL Server insert default values method

DECLARE @Sequence table (Sequence int identity(1,1))

DECLARE @i int; SET @i = 0

DECLARE @StartDate datetime;

SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+

RIGHT('0'+convert(varchar,month(getdate())),2) + '01' AS DATETIME)

WHILE ( @i < 120)

BEGIN

INSERT @Sequence DEFAULT VALUES

SET @i = @i + 1

END

SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)

FROM @Sequence

/* Partial results:

MonthSequence

Jan 1 2012 12:00AM

Feb 1 2012 12:00AM

Mar 1 2012 12:00AM

Apr 1 2012 12:00AM

------------

-- SQL Server Server datetime internal storage - SQL Server datetime formats

------------

-- SQL Server datetime to hex

SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))

/* Results

Now HexNow

2009-01-02 17:35:59.297 0x00009B850122092D

-- SQL Server date part - left 4 bytes - Days since 1900-01-01

SELECT Now=DATEADD(DAY, CONVERT(INT, 0x00009B85), '19000101')

-- Result: 2009-01-02 00:00:00.000

-- SQL time part - right 4 bytes - milliseconds since midnight

-- 1000/300 is an adjustment factor

-- SQL dateadd to Midnight

SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), '2009-01-02')

-- Result: 2009-01-02 17:35:59.290

------------

-- String date and datetime date&time columns usage
-- SQL Server datetime formats in tables

------------

USE tempdb;

SET NOCOUNT ON;

-- SQL Server select into table create

SELECT TOP (5)

FullName=convert(nvarchar(50),FirstName+' '+LastName),

BirthDate = CONVERT(char(8), BirthDate,112),

ModifiedDate = getdate()

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

ON c.ContactID = e.ContactID

ORDER BY EmployeeID

-- SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

/* Results

Table definition for the Employee table

Note: BirthDate is string date (only)

CREATE TABLE dbo.Employee(

FullName nvarchar(50) NOT NULL PRIMARY KEY,

BirthDate char(8) NULL,

ModifiedDate datetime NOT NULL

)

SELECT * FROM Employee ORDER BY FullName

/* Results

FullName BirthDate ModifiedDate

Guy Gilbert 19720515 2009-01-03 10:10:19.217

Kevin Brown 19770603 2009-01-03 10:10:19.217

Rob Walters 19650123 2009-01-03 10:10:19.217

Roberto Tamburello 19641213 2009-01-03 10:10:19.217

Thierry D'Hers 19490829 2009-01-03 10:10:19.217

-- SQL Server age

SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),

RowMaintenanceDate = CAST (ModifiedDate AS varchar)

FROM Employee ORDER BY FullName

/* Results

FullName Age RowMaintenanceDate

Guy Gilbert 37 Jan 3 2009 10:10AM

Kevin Brown 32 Jan 3 2009 10:10AM

Rob Walters 44 Jan 3 2009 10:10AM

Roberto Tamburello 45 Jan 3 2009 10:10AM

Thierry D'Hers 60 Jan 3 2009 10:10AM

-- SQL Server age of Rob Walters on specific dates

-- SQL Server string to datetime implicit conversion with DATEADD

SELECT AGE50DATE = DATEADD(YY, 50, '19650123')

-- Result: 2015-01-23 00:00:00.000

-- SQL Server datetime to string, Italian format for ModifiedDate

-- SQL Server string to datetime implicit conversion with DATEDIFF

SELECT FullName,

AgeDEC31 = DATEDIFF(YEAR, BirthDate, '20141231'),

AgeJAN01 = DATEDIFF(YEAR, BirthDate, '20150101'),

AgeJAN23 = DATEDIFF(YEAR, BirthDate, '20150123'),

AgeJAN24 = DATEDIFF(YEAR, BirthDate, '20150124'),

ModDate = CONVERT(varchar, ModifiedDate, 105)

FROM Employee

WHERE FullName = 'Rob Walters'

ORDER BY FullName

/* Results

Important Note: age increments on Jan 1 (not as commonly calculated)

FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDate

Rob Walters 49 50 50 50 03-01-2009 */

------------

-- SQL combine integer date & time into datetime

------------

-- Datetime format sql

-- SQL stuff

DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,

DateAsINT int,

TimeAsINT int

)

-- NOTE: leading zeroes in time is for readability only!

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)

INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)

SELECT DateAsINT, TimeAsINT,

CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ' '+

STUFF(STUFF ( RIGHT(REPLICATE('0', 6) + CONVERT(varchar(6), TimeAsINT), 6),

3, 0, ':'), 6, 0, ':')) AS DateTimeValue

FROM @DateTimeAsINT

ORDER BY ID

/* Results

DateAsINT TimeAsINT DateTimeValue

20121023 235959 2012-10-23 23:59:59.000

20121023 10204 2012-10-23 01:02:04.000

20121023 2350 2012-10-23 00:23:50.000

20121023 244 2012-10-23 00:02:44.000

20121023 50 2012-10-23 00:00:50.000

20121023 6 2012-10-23 00:00:06.000

------------

-- SQL Server string to datetime, implicit conversion with assignment

UPDATE Employee SET ModifiedDate = '20150123'

WHERE FullName = 'Rob Walters'

SELECT ModifiedDate FROM Employee WHERE FullName = 'Rob Walters'

-- Result: 2015-01-23 00:00:00.000

/* SQL string date, assemble string date from datetime parts */

-- SQL Server cast string to datetime - sql convert string date

-- SQL Server number to varchar conversion

-- SQL Server leading zeroes for month and day

-- SQL Server right string function

UPDATE Employee SET BirthDate =

CONVERT(char(4),YEAR(CAST('1965-01-23' as DATETIME)))+

RIGHT('0'+CONVERT(varchar,MONTH(CAST('1965-01-23' as DATETIME))),2)+

RIGHT('0'+CONVERT(varchar,DAY(CAST('1965-01-23' as DATETIME))),2)

WHERE FullName = 'Rob Walters'

SELECT BirthDate FROM Employee WHERE FullName = 'Rob Walters'

-- Result: 19650123

-- Perform cleanup action

DROP TABLE Employee

-- SQL nocount

SET NOCOUNT OFF;

------------

-- sql isdate function

------------

USE tempdb;

-- sql newid - random sort

SELECT top(3) SalesOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Sales.SalesOrderHeader

ORDER BY NEWID()

SELECT * FROM DateValidation

/* Results

SalesOrderID stringOrderDate

56720 Oct 26 2003 12:00AM

73737 Jun 25 2004 12:00AM

70573 May 14 2004 12:00AM

-- SQL update with top

UPDATE TOP(1) DateValidation

SET stringOrderDate = 'Apb 29 2004 12:00AM'

-- SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

/* Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an

out-of-range value.

-- sql isdate - filter for valid dates

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

/* Results

SalesOrderID OrderDate

73737 2004-06-25 00:00:00.000

70573 2004-05-14 00:00:00.000

-- SQL drop table

DROP TABLE DateValidation

------------

-- SELECT between two specified dates - assumption TIME part is 00:00:00.000

------------

-- SQL datetime between

-- SQL select between two dates

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= '1997-11-01' AND

RateChangeDate < DATEADD(dd,1,'1998-01-05')

/* Results

EmployeeID RateChangeDate

3 1997-12-12 00:00:00.000

4 1998-01-05 00:00:00.000

/* Equivalent to

-- SQL datetime range

SELECT EmployeeID, RateChangeDate

FROM AdventureWorks.HumanResources.EmployeePayHistory

WHERE RateChangeDate >= '1997-11-01 00:00:00' AND

RateChangeDate < '1998-01-06 00:00:00'

------------

-- SQL datetime language setting

-- SQL Nondeterministic function usage - result varies with language settings

SET LANGUAGE 'us_english'; –– Jan 12 2015 12:00AM

SELECT US = convert(VARCHAR,convert(DATETIME,'01/12/2015'));

SET LANGUAGE 'British'; –– Dec 1 2015 12:00AM

SELECT UK = convert(VARCHAR,convert(DATETIME,'01/12/2015'));

SET LANGUAGE 'German'; –– Dez 1 2015 12:00AM

SET LANGUAGE 'Deutsch'; –– Dez 1 2015 12:00AM

SELECT Germany = convert(VARCHAR,convert(DATETIME,'01/12/2015'));

SET LANGUAGE 'French'; –– déc 1 2015 12:00AM

SELECT France = convert(VARCHAR,convert(DATETIME,'01/12/2015'));

SET LANGUAGE 'Spanish'; –– Dic 1 2015 12:00AM

SELECT Spain = convert(VARCHAR,convert(DATETIME,'01/12/2015'));

SET LANGUAGE 'Hungarian'; –– jan 12 2015 12:00AM

SELECT Hungary = convert(VARCHAR,convert(DATETIME,'01/12/2015'));

SET LANGUAGE 'us_english';

GO------------

-- SQL Server 2008 T-SQL find next Monday for a given date

DECLARE @DateTime DATETIME = '2012-12-31'

SELECT NextMondaysDate=DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0),

WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0));

NextMondaysDate WeekDayName

2013-01-07 00:00:00.000 Monday

------------

-- Function for Monday dates calculation

------------

USE AdventureWorks2008;

-- SQL user-defined function

-- SQL scalar function - UDF

CREATE FUNCTION fnMondayDate

(@Year INT,

@Month INT,

@MondayOrdinal INT)

RETURNS DATETIME

BEGIN

DECLARE @FirstDayOfMonth CHAR(10),

@SeedDate CHAR(10)

SET @FirstDayOfMonth = convert(VARCHAR,@Year) + '-' + convert(VARCHAR,@Month) + '-01'

SET @SeedDate = '1900-01-01'

RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

@FirstDayOfMonth)) / 7 * 7, @SeedDate)

END

-- Test Datetime UDF - Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2016,2,3)

-- 2015-02-16 00:00:00.000

-- First Monday of current month

SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)

-- 2009-02-02 00:00:00.000

Wednesday 10 December 2014

Understanding ASP.NET MVC Filters and Attributes

Types of Filters

Authentication Filters

Authorization Filters

Action Filters

Result Filters

Exception Filters

Order of Filter Execution

Configuring Filters

Global level

Controller level

Action level

What do you think?

Tuesday 18 November 2014

Twitter Auth login using dot .Net C#

Sunday 16 November 2014

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.