Get Multitype datetime format in sql server

-- PRINT dbo.FN_FormatDateTime(Getdate(), 'LONGDATE')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'LONGDATEANDTIME')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'SHORTDATE')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'SHORTDATEANDTIME')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'UNIXTIMESTAMP')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'YYYYMMDD')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'YYYY-MM-DD')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'YYMMDD')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'YY-MM-DD')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'MMDDYY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'MM-DD-YY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'MM/DD/YY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'MM/DD/YYYY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'DDMMYY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'DD-MM-YY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'DD/MM/YY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'DD/MM/YYYY')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'HH:MM:SS 24')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'HH:MM 24')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'HH:MM:SS 12')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'HH:MM 12')
-- PRINT dbo.FN_FormatDateTime(Getdate(), 'ELSE')


CREATE FUNCTION [dbo].[FN_FormatDateTime]
(
    @dt DATETIME,
    @format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
    DECLARE @dtVC VARCHAR(64)
    SELECT @dtVC = CASE @format

    WHEN 'LONGDATE' THEN

        DATENAME(dw, @dt)
        + ',' + SPACE(1) + DATENAME(m, @dt)
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))

    WHEN 'LONGDATEANDTIME' THEN

        DATENAME(dw, @dt)
        + ',' + SPACE(1) + DATENAME(m, @dt)
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
        + SPACE(1) + RIGHT(CONVERT(CHAR(20),
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8),
        @dt, 112)), 22), 11)

    WHEN 'SHORTDATE' THEN

        LEFT(CONVERT(CHAR(19), @dt, 0), 11)

    WHEN 'SHORTDATEANDTIME' THEN

        REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),
            'AM', ' AM'), 'PM', ' PM')

    WHEN 'UNIXTIMESTAMP' THEN

        CAST(DATEDIFF(SECOND, '19700101', @dt)
        AS VARCHAR(64))

    WHEN 'YYYYMMDD' THEN

        CONVERT(CHAR(8), @dt, 112)

    WHEN 'YYYY-MM-DD' THEN

        CONVERT(CHAR(10), @dt, 23)

    WHEN 'YYMMDD' THEN

        CONVERT(VARCHAR(8), @dt, 12)

    WHEN 'YY-MM-DD' THEN

        STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),
        5, 0, '-'), 3, 0, '-')

    WHEN 'MMDDYY' THEN

        REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))

    WHEN 'MM-DD-YY' THEN

        CONVERT(CHAR(8), @dt, 10)

    WHEN 'MM/DD/YY' THEN

        CONVERT(CHAR(8), @dt, 1)

    WHEN 'MM/DD/YYYY' THEN

        CONVERT(CHAR(10), @dt, 101)

    WHEN 'DDMMYY' THEN

        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))

    WHEN 'DD-MM-YY' THEN

        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')

    WHEN 'DD/MM/YY' THEN

        CONVERT(CHAR(8), @dt, 3)

    WHEN 'DD/MM/YYYY' THEN

        CONVERT(CHAR(10), @dt, 103)

    WHEN 'HH:MM:SS 24' THEN

        CONVERT(CHAR(8), @dt, 8)

    WHEN 'HH:MM 24' THEN

        LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)

    WHEN 'HH:MM:SS 12' THEN

        LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))

    WHEN 'HH:MM 12' THEN

        LTRIM(SUBSTRING(CONVERT(
        VARCHAR(20), @dt, 22), 10, 5)
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))

    ELSE

        'Invalid format specified'

    END
    RETURN @dtVC
END

Split Multiple Strings and Insert to table.

--    Exec pr_str '6*88*2*10,7*99*4*9'

Create Procedure [dbo].[pr_str] @MultipleData Varchar(6000)
As

Declare @SplitMultipleData Varchar(50),@SplitMultipleDataToIndivisual Varchar(50)
Declare @Data1 BigInt,@Data2 NVarchar(50),@Data3 BigInt,@Data4 BigInt,@Counter BigInt


Declare MultipleData Cursor For 
Select Distinct String As D1D2D3D4 From FN_Split(@MultipleData,',') 
Open MultipleData                         
Fetch Next From MultipleData InTo @SplitMultipleData 
While @@FETCH_STATUS = 0                                 
Begin 
   

    Set @Counter = 1

    Declare SplitOnebyOne Cursor For 
    Select String As ParticularData From FN_Split(@SplitMultipleData,'*') 
    Open SplitOnebyOne                         
    Fetch Next From SplitOnebyOne InTo @SplitMultipleDataToIndivisual 
    While @@FETCH_STATUS = 0                                 
    Begin 
        If (@Counter=1)       
            Set @Data1 = Cast(@SplitMultipleDataToIndivisual As BigInt)

        If (@Counter=2)
            Set @Data2 = Cast(@SplitMultipleDataToIndivisual As NVarchar)

        If (@Counter=3)
            Set @Data3 = Cast(@SplitMultipleDataToIndivisual As BigInt)
           
        If (@Counter=4)
            Set @Data4 = Cast(@SplitMultipleDataToIndivisual As BigInt)

        Set @Counter = @Counter + 1   
   
   
        Fetch Next From SplitOnebyOne InTo @SplitMultipleDataToIndivisual 
    End                     
    Close SplitOnebyOne                           
    Deallocate SplitOnebyOne                             


insert into OMG (S_Name,S_Age,S_Sex,S_Hobbies) values
    ( @Data1 ,@Data2,@Data3, @Data4)


    Fetch Next From MultipleData InTo @SplitMultipleData 
End                     
Close MultipleData                           
Deallocate MultipleData


--    Select * FROM OMG


--  Truncate Table OMG 

Search Box

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Search Box</title>

<script src="../Resources/JSFiles/jquery-1.4.1.min.js" type="text/javascript"></script>
 <style type="text/css">
        .highlighted
        {
            background-color: yellow;
        }
        #search
        {
            width: 190px;
            height: 20px;
            border: 0px solid gray;
            background-color: #EDEDED;
            color: Black;
            font-weight: normal;
            font-size: 14px;           
        }
        #spanID:focus
        {
            border-color: #668681;
            -webkit-box-shadow: inset 0px 0px 10px 0px #ddd;
            -moz-box-shadow: inset 0px 0px 10px 0px #ddd;
            box-shadow: inset 0px 0px 10px 0px #ddd;
        }
        #searchText
        {
            width: 20px;
            background-color: Transparent;
            vertical-align: middle;
        }
        #spanID
        {
            background-color: #EDEDED;
            border: 1px solid #808080;
            float: right;
            width: 210px;
        }
    </style>
    <script type="text/javascript">
       function searchAndHighlight(searchTerm, selector) {
    if(searchTerm) {
        //var wholeWordOnly = new RegExp("\\g"+searchTerm+"\\g","ig"); //matches whole word only
        //var anyCharacter = new RegExp("\\g["+searchTerm+"]\\g","ig"); //matches any word with any of search chars characters
        var selector = selector || "body";                             //default selector is body if none provided
        var searchTermRegEx = new RegExp(searchTerm,"ig");
        var matches = $(selector).text().match(searchTermRegEx);
        if(matches) {
            $('.highlighted').removeClass('highlighted');     //Remove old search highlights           

            $(selector).html($(selector).html()
                    .replace(searchTermRegEx, "<span class='highlighted'>"+searchTerm+"</span>"));
            if($('.highlighted:first').length) {             //if match found, scroll to where the first one appears
                $(window).scrollTop($('.highlighted:first').position().top);               
            }
            return true;
        }
        else{
        alert("No results found");
        }
    }
    return false;
}
    </script>

</head>
<body>
    <div class="content" id="bodyContainer">
        <h1>
            <span id="spanID" style="float: right;"><input type="text" id="search" placeHolder="search..." onblur="javascript: searchAndHighlight($(this).val(), '#bodyContainer');" /><input type="image" src="images/zoom_in.png" title="search..." id="searchText" name="searchText" onclick="searchAndHighlight($('#search').val(), '#bodyContainer')" /></span>
        </h1>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
        <p>
            Input your text here.</p>
    </div>
    </div>
</body>
</html>

Get Months between two dates

Select top 12* FROM fn_getMonth(1)




-- Author: Nitish Jha -> 19 Dec 13.
-- If 0 Then Jan, Feb etc Else 1 to 12. 
Create function fn_getMonth (@DispType tinyint)     
Returns @Months Table (Months varchar(20))     
Begin     
DECLARE @startdt DATETIME, @enddt DATETIME     
Set @startdt =  (Select top 1 ActualStartDate from sessionmaster Where isDefault='Yes')     
Set @enddt =  (Select top 1 ActualEndDate from sessionmaster Where isDefault='Yes')     
    if (@DispType=0)   BEGIN
INSERT INTO @Months VALUES (Left(DateName(mm,@startdt),3))     
--INSERT INTO @Months VALUES (Month(@startdt))   
   
WHILE @startdt < @enddt     
BEGIN     
 SET  @startdt = DATEADD(MONTH,1,@startdt)     
 INSERT INTO @Months VALUES (Left(DateName(mm,@startdt),3))     
  --INSERT INTO @Months VALUES (Month(@startdt))   
END 
END
ELSE
BEGIN
INSERT INTO @Months VALUES (Month(@startdt))   
   
WHILE @startdt < @enddt     
BEGIN     
 SET  @startdt = DATEADD(MONTH,1,@startdt)     
  INSERT INTO @Months VALUES (Month(@startdt))   
END 
END
   
Return     
End


Cheers!!!

Query to remove duplicate records

CREATE TABLE [dbo].[ATTENDANCE](
    [EMPLOYEE_ID] [varchar](50) NOT NULL,
    [ATTENDANCE_DATE] [datetime] NOT NULL
) ON [PRIMARY]


INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A003',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES ('A004',CONVERT(DATETIME,'01-01-12',5))

-- First of All Create Identity.
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1) 

Select * FROM ATTENDANCE

-- Getting Duplicate Records.
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 
   
-- Deleting Duplicate Records.   
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Connection with Access, Excel and MDF (SQL SERVER)

Instructions::
1. Access File Name --  AccessDB_2007.accdb (2007)  & AccesDB_2003.mdb (97-2003 format) and Table name is - 'MemberMaster'.
2. Excel File Name --  ExcelDB_2007.xlsx (2007)  & ExcelDB_2003.xls (97-2003 format) and Sheet name is - 'MemberMaster'.
3. MDF File --  Database name SQLDB. Table Name -- MemberMaster.

      <<<<<<      If getting error while copying MDF then use these query in SQL SERVER. You have to copy mdf to App_Data folder.      >>>>>>>
    
            --    1.    --
                    USE MASTER; // Yeah its Master Database.

                    -- Take database in single user mode -- if you are facing errors.  
                    -- This may terminate your active transactions for database

                    ALTER DATABASE SQLDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

                    -- Detach DB
                    EXEC MASTER.dbo.sp_detach_db @dbname = N'SQLDB'

            --    2.    --
                    -- Attach again
                    CREATE DATABASE [SQLDB] ON
                    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQLDB.mdf' ),
                    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQLDB_log.ldf' )
                    FOR ATTACH

                    use SQLDB
                    Select * FROM MemberMaster

Tables:: Database and Table names are written in Connections strings.

ASPX Page

    <asp:TextBox ID="uid" runat="server" />
    <asp:TextBox ID="pwd" runat="server" />
    <asp:Button ID="submit" runat="server" Text="Submit" onclick="submit_Click" />






 C#

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.OleDb; // For Access & Excel.
using System.Data.SqlClient; // For SQL SERVER.

public partial class Programfiles_Access_login : System.Web.UI.Page
{
    //string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DataBase\AccesDB_2003.mdb;";  //Access 2003.
    string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DataBase\AccessDB_2007.accdb"; //Access 2007.

    //string conExl = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DataBase\ExcelDB_2003.xls;Extended Properties=Excel 8.0;";    //MS Excel 2003.
    //string conExl = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\DataBase\ExcelDB_2007.xlsx;Extended Properties=Excel 12.0;";    //MS Excel 2007.

    //string conSQL = @"Data Source=.;AttachDbFilename=SQLDB.mdf;User Instance=true; uid=sa;pwd=saa;"; // MDF Connection is best possible with SQL SERVER 2005.

  

    protected void Page_Load(object sender, EventArgs e)
    {
      
    }

    protected void submit_Click(object sender, EventArgs e)
    {
        #region AccessDB Connection
        if ((!string.IsNullOrEmpty(uid.Text)) && (!string.IsNullOrEmpty(pwd.Text)))
        {
            string qry = "Select * From MemberMaster where UserID='" + uid.Text + "' and Password='" + pwd.Text + "'";
            using (OleDbConnection con = new OleDbConnection(conn))
            {
                using (OleDbCommand cmd = new OleDbCommand(qry, con))
                {
                    con.Open();
                    OleDbDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            Session["Name"] = dr["FullName"].ToString();
                            Response.Redirect("Welcomepage.aspx");
                        }
                    }
                    if (dr.HasRows == false)
                    {
                        Response.Write("Either ID Or Password Is Incorrect.");
                    }
                }
            }
        }
        #endregion

        #region Excel Connection
        //if ((!string.IsNullOrEmpty(uid.Text)) && (!string.IsNullOrEmpty(pwd.Text)))
        //{
        //    string ExlSheet = "MemberMaster";// Name of Excel Sheet.;
        //    string qry = "Select * From " + "[" + ExlSheet + "$]" + " where UserID='" + uid.Text + "' and Password='" + pwd.Text + "'";
        //    using (OleDbConnection con = new OleDbConnection(conExl))
        //    {
        //        using (OleDbCommand cmd = new OleDbCommand(qry,con ))
        //        {
        //            con.Open();
        //            OleDbDataReader dr = cmd.ExecuteReader();
        //            while (dr.Read())
        //            {
        //                if (dr.HasRows == true)
        //                {
        //                    Session["Name"] = dr["FullName"].ToString();
        //                    Response.Redirect("Welcomepage.aspx");
        //                }
        //            }
        //            if (dr.HasRows == false)
        //            {
        //                Response.Write("Either ID Or Password Is Incorrect.");
        //            }
        //        }
        //    }
        //}
        #endregion

        #region Connection MDF (SQL Server)
        //if ((!string.IsNullOrEmpty(uid.Text)) && (!string.IsNullOrEmpty(pwd.Text)))
        //{
        //    string ExlSheet = "MemberMaster";// Excel Sheet name;
        //    string qry = "Select * From " + "[" + ExlSheet + "$]" + " where UserID='" + uid.Text + "' and Password='" + pwd.Text + "'";
        //    using (SqlConnection con = new SqlConnection(conSQL))
        //    {
        //        using (SqlCommand cmd = new SqlCommand(qry, con))
        //        {
        //            con.Open();
        //            SqlDataReader dr = cmd.ExecuteReader();
        //            while (dr.Read())
        //            {
        //                if (dr.HasRows == true)
        //                {
        //                    Session["Name"] = dr["FullName"].ToString();
        //                    Response.Redirect("Welcomepage.aspx");
        //                }
        //            }
        //            if (dr.HasRows == false)
        //            {
        //                Response.Write("Either ID Or Password Is Incorrect.");
        //            }
        //        }
        //    }
        //}
        #endregion
    }
}


Cheers!!!

Calculate Age in SQL Server

-- select dbo.FN_CalculateAge('1/21/1990') doB   -- DD/MM/YYYY.
Create FUNCTION [dbo].[FN_CalculateAge](@dayOfBirth datetime)
RETURNS Varchar(300)
AS 
BEGIN 
 
 DECLARE @Age varchar(300)
 DECLARE @today datetime, @thisYearBirthDay datetime
 DECLARE @years int, @months int, @days int 
 SELECT @today = GETDATE() 

 SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth) 
 SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END) 
 SELECT @months = MONTH(@today - @thisYearBirthDay) - 1 
 SELECT @days = DAY(@today - @thisYearBirthDay) - 1 
 Set @Age=Cast(@years  As Varchar)+' years '+ Cast(@months  As Varchar)+' months '+Cast(@days As Varchar) +' days'
RETURN @Age 
END