Move and remove Listbox item to another listbox using jQuery/ JavaScript

// Option 1: jQuery


<script type="text/javascript" language="javascript">
$(document).keydown(function (e) {
    var element = e.target.nodeName.toLowerCase();
    if (element != 'input' && element != 'textarea') {
        if (e.keyCode === 8) {
            return false;
        }
    }
});

$(document).ready(function () {
                
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    var sAllValue = '';
    $('#<%=lstAssigenedModules.ClientID %> option').each(function () {

    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";

    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);

   
    $('#<%=lstAllModules.ClientID %>').click(function () {
    if ($('#<%=lstAllModules.ClientID %> > option:selected').val() != '')
    $('#btnAddModule').removeAttr('disabled');
    else
    $('#btnAddModule').attr('disabled', 'disabled');
    });
    $('#<%=lstAssigenedModules.ClientID %>').click(function () {
    if ($('#<%=lstAssigenedModules.ClientID %> > option:selected').val() != '')
    $('#btnRemModule').removeAttr('disabled');
    else
    $('#btnRemModule').attr('disabled', 'disabled');
    });
    
    $('#btnAddModule').click(
    function (e) {
    if ($('#<%=lstAllModules.ClientID %> > option:selected').appendTo($('#<%=lstAssigenedModules.ClientID %>')));
    $('#btnAddModule').attr('disabled', 'disabled');

    var sAllValue = '';
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    $('#<%=lstAssigenedModules.ClientID %> > option').each(function () {
    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";
    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);
    });
   
    $('#btnRemModule').click(function (e) {
    if ($('#<%=lstAssigenedModules.ClientID %> > option:selected').appendTo($('#<%=lstAllModules.ClientID %>')));
    $('#btnRemModule').attr('disabled', 'disabled');
        
    $("#<%=hdnAssignedModules.ClientID%>").val('');
    var sAllValue = '';
    $('#<%=lstAssigenedModules.ClientID %> > option').each(function () {
    var vImgValue = $(this).val();
    sAllValue = sAllValue + vImgValue + ";";
    });
    $("#<%=hdnAssignedModules.ClientID%>").val(sAllValue);
    });
 });    



 </script>



 // Option 2: Javascript
  <script type="text/javascript" language="javascript">
        function move(tbFrom, tbTo) {
            var arrFrom = new Array(); var arrTo = new Array();
            var arrLU = new Array();
            var i;
            for (i = 0; i < tbTo.options.length; i++) {
                arrLU[tbTo.options[i].text] = tbTo.options[i].value;
                arrTo[i] = tbTo.options[i].text;
            }
            var fLength = 0;
            var tLength = arrTo.length;
            for (i = 0; i < tbFrom.options.length; i++) {
                arrLU[tbFrom.options[i].text] = tbFrom.options[i].value;
                if (tbFrom.options[i].selected && tbFrom.options[i].value != "") {
                    arrTo[tLength] = tbFrom.options[i].text;
                    tLength++;
                }
                else {
                    arrFrom[fLength] = tbFrom.options[i].text;
                    fLength++;
                }
            }

            tbFrom.length = 0;
            tbTo.length = 0;
            var ii;

            for (ii = 0; ii < arrFrom.length; ii++) {
                var no = new Option();
                no.value = arrLU[arrFrom[ii]];
                no.text = arrFrom[ii];
                tbFrom[ii] = no;
            }

            for (ii = 0; ii < arrTo.length; ii++) {
                var no = new Option();
                no.value = arrLU[arrTo[ii]];
                no.text = arrTo[ii];
                tbTo[ii] = no;
            }

            document.getElementById("SiteBody_hdnAssignedModules").value = "";
            var sAllValue = '';
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            for (i = 0; i < list.options.length; i++) {
                sAllValue = sAllValue + list.options[i].value + ";";
            }
            document.getElementById("SiteBody_hdnAssignedModules").value = sAllValue;

            document.getElementById("btnAddModule").disabled = true;
            document.getElementById("btnRemModule").disabled = true;
        }

        function enableAddButton() {
            var list = document.getElementById('SiteBody_lstAllModules');
            if (list.options.length != 0) {

                var indx = list.selectedIndex;

                if (list[indx].value != '')
                    document.getElementById("btnAddModule").disabled = false;
                else
                    document.getElementById("btnAddModule").disabled = true;
            }
        }

        function enableRemoveButton() {
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            if (list.options.length != 0) {
                var indx = list.selectedIndex;

                if (list[indx].value != '')
                    document.getElementById("btnRemModule").disabled = false;
                else
                    document.getElementById("btnRemModule").disabled = true;
            }
        }

        function SetAssignedModules() {
            document.getElementById("SiteBody_hdnAssignedModules").value = "";
            var sAllValue = '';
            var list = document.getElementById('SiteBody_lstAssigenedModules');
            for (i = 0; i < list.options.length; i++) {
                sAllValue = sAllValue + list.options[i].value + ";";
            }
            document.getElementById("SiteBody_hdnAssignedModules").value = sAllValue;
        }
    </script>
    
    
    
    PAGE:
    
<tr>
<td>
<asp:ListBox ID="lstAllModules" onClick="enableAddButton()" runat="server"></asp:ListBox>
</td>
<td>
<input type="button" id="btnAddModule" value="Add &gt;&gt;" style="width: 90px;"
disabled="disabled" onclick="move(this.form.SiteBody_lstAllModules,this.form.SiteBody_lstAssigenedModules)" />
<input type="button" id="btnRemModule" value="&lt;&lt; Remove" style="width: 90px;"
disabled="disabled" onclick="move(this.form.SiteBody_lstAssigenedModules,this.form.SiteBody_lstAllModules)" />
</td>
<td>
<asp:ListBox ID="lstAssigenedModules" onClick="enableRemoveButton()" runat="server">
</asp:ListBox>
<asp:HiddenField ID="hdnAssignedModules" runat="server" />
</td>
</tr>


NOTE:

* SetAssignedModules()-> Setting dropdown values to hidden feilds.
* Change Controls' Id.

Finding Unmatched Records in DatatTables Using Linq

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;



namespace MyApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("Bay", typeof(string));
            dt1.Rows.Add("S30");
            dt1.Rows.Add("S31");
            //dt1.Rows.Add("S32");

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("Bay", typeof(string));
            dt2.Rows.Add("S31");
            dt2.Rows.Add("S41");
            dt2.Rows.Add("S51");

            var vardt1 = dt1.AsEnumerable().Select(a => a.Field<string>("Bay"));
            var vardt2 = dt2.AsEnumerable().Select(a => a.Field<string>("Bay"));
            var varUnMatched = vardt1.Except(vardt2);
            
            Response.Write(varUnMatched.Count().ToString()+"<br/>");
            foreach (var element in varUnMatched)
            {
                Response.Write(element + "<br/>");
            }
        }
    }
}



Cheers!!!

Is SQL Server Case-Sencitive? If Yes, then How to compare to values.

-- Example of case-sencivity.
Declare @v1 varchar(20), @v2 varchar(20)

SET @v1='nitisH' ; SET @v2='niTisH'

IF(@v1=@v2 COLLATE Latin1_General_CS_AS )
    PRINT 'Matched'
Else
    PRINT 'Unmatched'   
   
   
   

Select Name from MemberMaster where Name = 'niTisH' COLLATE Latin1_General_CS_AS


Keyword ->

COLLATE: Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression
         to apply a collation cast.

        The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
       
        You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also
        specify a collation when you create a database using SQL Server Management Studio. If you do not specify a collation, the database is assigned the
        default collation of the instance of SQL Server.   
       
        You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify
        a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation
        of the database. You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation
        default of the current user database for the connection instead of tempdb.   
       
        You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation
        of the current database. Column references are assigned the definition collation of the column.
       
       
database_default: Causes the COLLATE clause to inherit the collation of the current database.   

collation_name: Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified
        Windows_collation_name or a SQL_collation_name. collation_name must be a literal value. collation_name cannot be represented by a variable or expression.

CaseSensitivity: CI specifies case-insensitive, CS specifies case-sensitive.
AccentSensitivity: AI specifies accent-insensitive, AS specifies accent-sensitive.



/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('test_base', 'Collation')

/* Find Collation of SQL Server Database Table Column */
USE test_base

SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'EmployeeDetails')
--AND name = 'City' -- > can apply over particular column too.


Cheers!!!


Ref:

http://msdn.microsoft.com/en-us/library/ms184391.aspx
http://sqlservernet.blogspot.in/2012/02/case-sensitive-sql-query-search-and.html
http://msdn.microsoft.com/en-us/library/ms180175(v=sql.105).aspx

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%';

How to get QueryStirng on client side like ASPX or html etc

 function getQueryString(name) {
        name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]");
        var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"),
        results = regex.exec(location.search);
        return results === null ? "" : decodeURIComponent(results[1].replace(/\+/g, " "));
    }


Call this method as :
    var ClassSectionID = getQueryString('Classsectionid');



Cheers!!!

Get total working days excluding sunday and 5th Saturday of any monthhs in SQL SERVER

/*
DESCRIPTION:: Used to get total working days excluding sunday and 5th Saturday of any monthhs.
Created By : NITISH KUMAR
DATE:: 21 Aug, 14
SELECT dbo.fn_getWorkingDays('1 Mar 2014','6 May 2014') as WD
*/
Create function fn_getWorkingDays (@DateFrom smalldatetime, @DateTo smalldatetime)
Returns varchar(30)
AS
BEGIN
DECLARE @FMonth varchar(20), @TMonth varchar(20), @FYear varchar(10), @TYear varchar(10), @WD bigint, @Count varchar(10), @tot varchar(10)
--SET @DateFrom='1 Mar 2014';
--SET @DateTo='31 May 2014';
--SET NOCOUNT ON
Declare  @dates Table(totdates datetime)

WITH T(date)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DateAdd(day,1,T.date) date FROM T WHERE T.date < @DateTo
) Insert INTO @dates Select * FROM T OPTION (MAXRECURSION 32767);

--SELECT * FROM @dates OPTION (MAXRECURSION 32767);
SET @Count='0';SET @tot='0';
DECLARE CUR CURSOR FOR SELECT distinct DATEPART(YYYY,totdates) M,DATEPART(MM,totdates) Y FROM @dates  OPTION (MAXRECURSION 32767);
OPEN CUR
Fetch NEXT FROM CUR INTO @FYear, @FMonth
While @@FETCH_STATUS=0
BEGIN
    SELECT @Count=Count(*) FROM @dates Where DATENAME(DW,totdates)='Saturday' and DatePart(MM,totdates)=@FMonth and DATEPART(YYYY,totdates) =@FYear OPTION (MAXRECURSION 32767);
   
    IF(CAST(@Count as bigint) =5)
    BEGIN
        IF(ISNULL(@tot,0) ='0')
            SET @tot = 1
        ELSE
            SET @tot = Cast(@tot as bigint) + 1;
    END           
Fetch NEXT FROM CUR INTO @FYear, @FMonth
END
CLOSE CUR
DEALLOCATE CUR

SELECT @WD=Count(*) FROM @dates OPTION (MAXRECURSION 32767);
SET @Count='';
SELECT @Count=Count(*) FROM @dates Where DATENAME(DW,totdates)='Sunday' OPTION (MAXRECURSION 32767);
SET @tot = Cast(@tot as bigint) + Cast(@Count as bigint)
SET @tot= @WD- Cast(@tot as bigint)
--SET NOCOUNT OFF
--PRINT @tot
return @tot
END


NOTE::

The MAXRECURSION query hint specifies the maximum number of recursions allowed for a query. The number of recursions is a non-negative integer between 0 and 32,767. When 0 is specified, no limit is applied.

If the MAXRECURSION query hint is not specified, the default limit is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

CHEERS!!!

Show Number of Characters Remaining in Textbox or Textarea inside gridview.

Hello dear, 

    This operation can easily perform but it became tedious task when you have to do this for gridview controls (textbox) and evvn much tedious when there is
    multiple textbox with same ids (in case of multiple rows in gridview).  I have aslo faced this situation and after expendign more then 5 hrs I came to solve
    this problem. Here is the code. This may help you!
   
   
   
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Remark</title>
  

    <script src="../../../Resources/JSFiles/jquery-1.7.2.min.js" type="text/javascript"></script>

  
    <script type="text/javascript">
        $(function() {
            var maxlength = 120;
            $("[id^=grStudentRemark] input[type=text]").on("keypress", function(e) {
                var Len = $(this).val().length;
                var remChar = maxlength - Len;

                if (remChar > -1) {
                    $(this).next('span').text(remChar);
                }
                if (Len > maxlength) {
                    //e.preventDefault();
                    $(this).val($(this).val().substring(0, maxlength));
                }
            });
        });
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>

          
        <asp:GridView ID="grStudentRemark" runat="server" AutoGenerateColumns="False" BackColor="White"
            Visible="false" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
           GridLines="Vertical">
            <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
            <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#000000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="#DCDCDC" />
            <Columns>
                <asp:TemplateField HeaderText="Remark">
                    <ItemTemplate>
                       <asp:TextBox ID="txtMultipleRemark1"
                            MaxLength="200" runat="server" Width="350px" Style="margin-bottom: 1px;" /><asp:Label
                                ID="r1" runat="server" Style="background-color: #E2EEF1; color: Red; font-weight: normal;">120</asp:Label><br />
                       <asp:TextBox ID="txtMultipleRemark2"
                            MaxLength="200" runat="server" Width="350px" Style="margin-bottom: 1px;" /><asp:Label
                                ID="r2" runat="server" Style="background-color: #E2EEF1; color: Red; font-weight: normal;">120</asp:Label><br />
                       <asp:TextBox ID="txtMultipleRemark3"
                            MaxLength="200" runat="server" Width="350px" Style="margin-bottom: 1px;" /><asp:Label
                                ID="r3" runat="server" Style="background-color: #E2EEF1; color: Red; font-weight: normal;">120</asp:Label><br />
                       <asp:TextBox ID="txtMultipleRemark4"
                            MaxLength="200" runat="server" Width="350px" Style="margin-bottom: 1px;" /><asp:Label
                                ID="r4" runat="server" Style="background-color: #E2EEF1; color: Red; font-weight: normal;">120</asp:Label><br />
                       <asp:TextBox ID="txtMultipleRemark5"
                            MaxLength="200" runat="server" Width="350px" Style="margin-bottom: 1px;" /><asp:Label
                                ID="r5" runat="server" Style="background-color: #E2EEF1; color: Red; font-weight: normal;">120</asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
    </asp:GridView>
               
    </div>
    </form>
</body>
</html>



Cheers!!!

Count number of left and right child

Select * FROM MLM



--    exec pr_CntLandR '1'
Create proc pr_CntLandR @UID varchar(10)
as
with Traverse as
(
  select
    UID, cast(POS as varchar(max)) Nodes
  from
    dbo.MLM c
  where
    P_ID = 0
  union all
  select
    c.UID, r.Nodes + CAST(c.POS as varchar)
  from
    dbo.MLM c
  join
    Traverse r on r.UID = c.P_ID
)
select
  t1.UID,-- u.P_ID,
  (select COUNT(*) from Traverse t2 where t1.Nodes + '1' = LEFT(t2.Nodes, len(t1.Nodes) + 1)) LeftCount,
  (select COUNT(*) from Traverse t2 where t1.Nodes + '2' = LEFT(t2.Nodes, len(t1.Nodes) + 1)) RightCount
from
  Traverse t1
join
  dbo.MLM u on u.UID = t1.UID
 where t1.UID=@UID
 order by
  t1.UID
option (maxrecursion 0)






Note: 1-> Left & 2-> Right childs.

Resize image and show

<form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="btnUpload" Text="Upload" runat="server" OnClick="btnUpload_Click" />
        <asp:ImageButton ID="imgBtn" runat="server" ToolTip="To download right click 'Save Image As'" />
    </div>
</form>




using System;
using System.Collections;
using System.Configuration;
using System.Data;
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.IO;
using System.Text;
using System.ComponentModel;
using System.Drawing;
using System.Collections.Specialized;
using System.Drawing.Imaging;


public partial class ProgramFiles_OnlineRegistrationForm_ImageResizer : System.Web.UI.Page
{
   
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {          
            string FilePath = System.Configuration.ConfigurationManager.AppSettings["UpcomingEventPath"].ToString();
            Session["PhotoGalleryPath"] = FilePath;
        }
    }
   
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string FileName1 = "";
        if (FileUpload1.HasFile == true)
        {
            FileName1 = FileUpload1.FileName.ToUpper(); ;
            string Extension = Path.GetExtension(FileName1);

            if (Extension == ".JPG" || Extension == ".JPEG" || Extension == ".PNG" || Extension == ".GIF" || Extension == ".BMP")
            {
                if (File.Exists(Session["PhotoGalleryPath"].ToString()))
                {
                    File.Delete(Session["PhotoGalleryPath"].ToString());
                }
                else
                {
                    FileUpload1.SaveAs(Server.MapPath((System.Configuration.ConfigurationManager.AppSettings["UpcomingEventPath"].ToString())) + FileName1);


                    string path = Server.MapPath((System.Configuration.ConfigurationManager.AppSettings["UpcomingEventPath"].ToString())) + FileName1;
                   
                    System.Drawing.Image image = System.Drawing.Image.FromFile(path);
                    using (System.Drawing.Image thumbnail = image.GetThumbnailImage(100, 120, new System.Drawing.Image.GetThumbnailImageAbort(ThumbnailCallback), IntPtr.Zero))
                    {
                        using (MemoryStream memoryStream = new MemoryStream())
                        {
                            thumbnail.Save(memoryStream, ImageFormat.Png);
                            Byte[] bytes = new Byte[memoryStream.Length];
                            memoryStream.Position = 0;
                            memoryStream.Read(bytes, 0, (int)bytes.Length);
                            string base64String = Convert.ToBase64String(bytes, 0, bytes.Length);
                            imgBtn.ImageUrl = "data:image/png;base64," + base64String;
                            imgBtn.Visible = true;
                        }
                    }
                }
            }
        }
    }

    public bool ThumbnailCallback()
    {
        return false;
    }
}



Note :

    1. Add <add key="UpcomingEventPath" value="\DemoSite\UserSpace\UpcomingEvents\"/> in <appSettings> @ web.Congic
    2. Create two folder inside your project (DemoSite) USerSpace & UpcomingEvents.
    3. You can save this resized image by right click and save as on that thumbnal.


Cheers!!!

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