How to Convert any Date format into sql date format

alibasha

SOLVED CLOSED ACTIVE ANSWERED
User: alibasha
Posted: on Aug 10, 2012 08:27 AM
Forum: .Net Basics
Replies: 4
Views: 6806

I am having one String Array which consists oif string date like 15/07/2012, where 07 is the month,I need to convert it into sql Date Formar like 2012-07-15 09:46:51.000 that is year/mm/dd and time for that I am trying use this line of code

IFormatProvider culture = new CultureInfo("en-US", true);

objar.Add(DateTime.ParseExact((s[1]), "dd-MM-yyyy HH:mm:ss.fff", culture, DateTimeStyles.None));

I am not able to get solution for the last 2 hours,please me in this reg as soon as possible.

Please have a look into my code.

private void LoadandSaveXMLData()
        {
            Date = txtDate.Text;
            ChkNDt = chkvalue + txtDate.Text;

            string ListofXMLValues = "";
            string parentElementName = "";
            string childElementName = "";
            string childElementValue = "";
            bool element = false;
            lblMsg.Text = "";

            //if XML file present in local system
            //XmlTextReader xReader = new XmlTextReader(Server.MapPath("~/POST0520120803.xml"));
            //if XML file present in Remote Server system
            //XmlTextReader xReader = new XmlTextReader("http://www.godurian.com/godurian/sth100/transfiles/POST0520120803.xml");
            XmlTextReader xReader = new XmlTextReader("http://www.godurian.com/godurian/sth100/transfiles/POST" + ChkNDt + ".xml");
           
                try
                {
                    while (xReader.Read())
                    {
                        if (xReader.NodeType == XmlNodeType.Element)
                        {
                            if (element)
                            {
                                parentElementName = parentElementName + childElementName + " ";
                                // parentElementName = parentElementName + "<br>";

                            }
                            element = true;
                            childElementName = xReader.Name;
                        }
                        else if (xReader.NodeType == XmlNodeType.Text | xReader.NodeType == XmlNodeType.CDATA)
                        {

                            element = false;
                            childElementValue = xReader.Value;
                            //lblMsg.Text = lblMsg.Text + "<b>" + parentElementName + "<br>" + childElementName + "</b><br>" + childElementValue;

                            lblMsg.Text = lblMsg.Text + "" + parentElementName + "" + childElementValue + ",";

                            //lblMsg.Text = childElementName + " " + lblMsg.Text + "" + childElementValue + ",";

                            ListofXMLValues = (lblMsg.Text);


                            parentElementName = "";
                            childElementName = "";
                        }
                        ListofXMLValues = ListofXMLValues.TrimEnd(',');
                        lblXMLList.Text = ListofXMLValues;


                    }
                    if (ListofXMLValues != "")
                    {
                        string XMLValuesWithoutPN = ListofXMLValues.Remove(0, 8);
                        string[] XMLValuesResult = Regex.Split(XMLValuesWithoutPN, "adddailyrevenue ");

                        foreach (string a in XMLValuesResult)
                        {
                            if (a != "")
                            {
                                string CurrentUser = Session["admin"].ToString();
                                string[] s = Regex.Split(a, ",");
                                IFormatProvider culture = new CultureInfo("en-US", true);
                                ArrayList objar = new ArrayList();
                                objar.Add(s[0]);
                                objar.Add(DateTime.ParseExact((s[1]), "dd-MM-yyyy HH:mm:ss.fff", culture, DateTimeStyles.None));
                                objar.Add(s[2]);
                                //objar.Add(s[3]);
                                objar.Add(DateTime.ParseExact((s[3]), "dd-MM-yyyy HH:mm:ss.fff", culture, DateTimeStyles.None));
                                objar.Add(s[4]);
                                objar.Add(s[5]);
                                objar.Add(s[6]);
                                objar.Add(s[7]);
                                objar.Add(s[8]);
                                objar.Add(s[9]);
                                objar.Add(s[10]);
                                objar.Add(s[11]);
                                objar.Add(s[12]);
                                objar.Add(s[13]);
                                objar.Add(s[14]);
                                objar.Add(s[15]);
                                objar.Add(s[16]);
                                objar.Add(s[17]);
                                objar.Add(s[18]);
                                objar.Add(s[19]);
                                objar.Add(s[20]);
                                objar.Add(s[21]);
                                objar.Add(s[22]);
                                objar.Add(s[23]);
                                objar.Add(s[24]);
                                objar.Add(CurrentUser);
                                DBUtil.DbUtilResult dbResult = DBUtil.Execute("SPInsertXMLData", objar);
                                int iResult = Convert.ToInt16(dbResult.Params["@RETURN_VALUE"]);
                                if (iResult > 0)
                                {

                                    CustomMessage.ShowMessage("Saved Sucessfully");
                                }
                                else if (iResult == 0)
                                {
                                    CustomMessage.ShowMessage("One or More Records Already Exists");
                                }
                                else
                                {
                                    CustomMessage.ShowMessage("Error in Saving");
                                }
                            }
                        }

                    }
                    xReader.Close();

                }
                
            
            
        catch (Exception)
                {

                    CustomMessage.ShowMessage("Specified file not found");
                }

        }

Mudassar

0 1

Reply
User: Mudassar
Replied: on Aug 10, 2012 08:32 AM
Report

Please share the stored procedure

alibasha

0 2

Reply
User: alibasha
Replied: on Aug 10, 2012 08:49 AM
Report

It s[0,1,...] is not the stored procedure,it is string array,please observer the line

string[] s = Regex.Split(a, ",");

I just need to convert to input Date format from dd/mm/yyyy hh.mm.ss.ffff to yyyy/mm/dd hh.mm.ss.ffff(Sql Server Fomat),in my another page I was able to convert successfully but in that case time(hh;mm:ss.fff) was not there,so it had worked fine(EG:objarr.Add(DateTime.ParseExact(txtTdt.Text, "dd-MM-yyyy", null));)

but here...... objar.Add(DateTime.ParseExact((s[1]), "dd-MM-yyyy HH:mm:ss.fff", culture, DateTimeStyles.None)); I am getting exception....please help as soon as possible.

alibasha

0 3

Reply
User: alibasha
Replied: on Aug 10, 2012 09:54 AM
Report

Sorry I thought that you might have think array string as SP,please find Stored Procedure below...

USE [STHDB]
GO
/****** Object:  StoredProcedure [dbo].[SPInsertXMLData]    Script Date: 08/10/2012 21:23:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPInsertXMLData]
@OutletCode nvarchar(3)=null,
@OutletDailySalesDate datetime=null,
@ReferenceNo nvarchar(10)=null,
@TransactionTime datetime=null,
@NonNettSalesItem nvarchar(10)=null,
@NettSalesItem nvarchar(10)=null,
@AmountBeforeSalesItemDiscount decimal(18,2)=null,
@AmountAfterSalesItemDiscount decimal(18,2)=null,
@SalesDiscountPercentage nvarchar(10)=null,
@SalesDiscountValue nvarchar(10)=null,
@SalesDiscountAll nvarchar(10)=null,
@AmountAfterTotalDiscount decimal(18,2)=null,
@ServiceTypeChargeInValue nvarchar(10)=null,
@GovernmentTaxChargeInValue nvarchar(10)=null,
@SalesGrandTotal decimal(18,2)=null,
@SalesGrandTotalRounding decimal(18,2)=null,
@VATSales nvarchar(10)=null,
@VATExemptSales nvarchar(10)=null,
@VATZeroRatedSales nvarchar(10)=null,
@VATAmount decimal(18,2)=null,
@CashPayment nvarchar(10)=null,
@CreditCardPayment nvarchar(10)=null,
@CreditorAccountPayment nvarchar(10)=null,
@CashVoucherPayment nvarchar(10)=null,
@MemberID nvarchar(20)=null,
@CreatedBy nvarchar(50)=null



AS
BEGIN
if exists(select * from tbl_Transactions where ReferenceNo=@ReferenceNo)
Return 0
else
begin
	INSERT INTO [tbl_Transactions]
           ([OutletCode] 
           ,[OutletDailySalesDate] 
           ,[ReferenceNo] 
           ,[TransactionTime]
           ,[NonNettSalesItem] 
           ,[NettSalesItem] 
           ,[AmountBeforeSalesItemDiscount] 
           ,[AmountAfterSalesItemDiscount] 
           ,[SalesDiscountPercentage] 
           ,[SalesDiscountValue] 
           ,[SalesDiscountAll]  
           ,[AmountAfterTotalDiscount]
           ,[ServiceTypeChargeInValue] 
           ,[GovernmentTaxChargeInValue] 
           ,[SalesGrandTotal] 
           ,[SalesGrandTotalRounding] 
           ,[VATSales] 
           ,[VATExemptSales] 
           ,[VATZeroRatedSales] 
           ,[VATAmount] 
           ,[CashPayment] 
           ,[CreditCardPayment] 
           ,[CreditorAccountPayment] 
           ,[CashVoucherPayment] 
           ,[MemberID] 
           ,[CreatedBy] 
           ,[CreatedDate]
           )
     VALUES
           (@OutletCode
           ,@OutletDailySalesDate
           ,@ReferenceNo
           ,@TransactionTime
           ,@NonNettSalesItem
           ,@NettSalesItem
           ,@AmountBeforeSalesItemDiscount
           ,@AmountAfterSalesItemDiscount
           ,@SalesDiscountPercentage
           ,@SalesDiscountValue
           ,@SalesDiscountAll
           ,@AmountAfterTotalDiscount
           ,@ServiceTypeChargeInValue
           ,@GovernmentTaxChargeInValue
           ,@SalesGrandTotal
           ,@SalesGrandTotalRounding 
           ,@VATSales
           ,@VATExemptSales
           ,@VATZeroRatedSales
           ,@VATAmount
           ,@CashPayment
           ,@CreditCardPayment
           ,@CreditorAccountPayment
           ,@CashVoucherPayment
           ,@MemberID
           ,@CreatedBy
           ,GETDATE())
           
     RETURN SCOPE_IDENTITY()
END
end

Mudassar

0 4

Reply
User: Mudassar
Replied: on Aug 10, 2012 07:02 PM
Report

Stored Procedure looks fine. are you getting any error?

How to Convert any Date format into sql date format

Company

Explore

Follow us

Contact Us