locked
dateTime Conversion From dd/mm/yyyy to mm/dd/yyyy while inserting RRS feed

  • Question

  • Hi All

    I am stuck we are in a maintenance team of a ASP Code and the C# Front end Code sends the date in dd/mm/yyyy But the Sql Server datetime format is set to mm/dd/yyyy Now our client wants to fix the bug without changing any Code in Front end C# he wants us to alter the procedure in SQL Server So that while inserting date from front end it is converted in Backend SQL server to mm/dd/yyyy I have tried to convert it using convert function and style set to 101 but still it doesnt work ... Kindly help me out how can I accept date in dd/mm/yyyy from front end and while inserting in table I can Convert it to mm/dd/yyyy ....Thanks in Advance


    rajdey1

    Sunday, July 8, 2012 5:11 AM

Answers

  • The easiest way is to use variable like:

    begin
    	DECLARE @dateFrom AS datetime,@dateTo as datetime;
    	SET @dateFrom = (SELECT convert(datetime,@effectiveFrom,101));
    	SET @dateTo = (SELECT convert(datetime,@effectiveTo,101));
    	
    	Insert into taxmaster 
    		(TaxCode ,TaxName ,TaxPc,effectiveFrom, effectiveTo) 
    	values 
    		(@TaxCode,@TaxName ,@TaxPc , @dateFrom, @dateTo)
     
    end


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Naomi N Sunday, July 8, 2012 3:21 PM
    • Unmarked as answer by Naomi N Sunday, July 8, 2012 3:21 PM
    • Proposed as answer by Naomi N Sunday, July 8, 2012 3:21 PM
    • Marked as answer by amber zhang Monday, July 16, 2012 8:07 AM
    Sunday, July 8, 2012 2:57 PM
  • this works with all version of SQL server

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by amber zhang Monday, July 16, 2012 8:07 AM
    Sunday, July 8, 2012 3:19 PM

All replies

  • Hello,

    You should use SqlParameter in you C# code, the Ado.Net takes care about the correct date format.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, July 8, 2012 8:28 AM
  • Hi Olaf as I mentioned I am not supposed to change any client code as per client direction this bug has to be fixed in backed ... Kindly help me in fixing it in backend

    rajdey1

    Sunday, July 8, 2012 8:57 AM
  • Then the only option is to change the default language of the used SQL account, e.g. from british to english_us

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, July 8, 2012 9:28 AM
  • hi

    thanks for the suggestion ... Cant We use Convert  to Convert  the date to "US" before inserting into table Because we are successful in converting date to dd/mm/yyyy while using select statement cant the same be done to convert the data before inserting using the following

    convert(datetime,"22/07/2012",101)


    rajdey1

    Sunday, July 8, 2012 10:46 AM
  • Yes, you can use the convert functíon, see http://msdn.microsoft.com/en-us/library/ms187928.aspx

    But this would mean you have to modify your application ... and if you do so, use SqlParameter instead


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, July 8, 2012 11:43 AM
  • Hi Olaf

     Sorry I Coudnt get why should I change in code ... The C# code from front end is sending it as string and the backend Procedure accepts it as @date Varchar(30) can I not change the date variable in procedure before inserting it... Kindly advice


    rajdey1

    Sunday, July 8, 2012 12:32 PM
  • If you have a stored procedure, then use the convert function there; so what's the problem here?

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, July 8, 2012 2:22 PM
  • i am finding it difficult to convert before inserting its giving me out of range error but while retrieving data i use convert i face no problem... but while converting before inserting i get the error

    rajdey1

    Sunday, July 8, 2012 2:27 PM
  • Can you please post the code (-part) of your stored procedure, because I still don't understand, what the problem is?

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, July 8, 2012 2:29 PM
  • Hi

    I am posting here a part of procedure

    Alter Procedure USP_ADDUPdate_Tax_Master (@flag int,@TaxCode varchar(10),@TaxName varchar(50),@TaxPc float,@effectiveFrom Varchar(30), @effectiveTo varchar(30))

    as

    Begin

    If(@flag=1)

    begin

    Insert into taxmaster (TaxCode ,TaxName ,TaxPc,effectiveFrom, effectiveTo) values (@TaxCode,@TaxName ,@TaxPc , convert(datetime,@effectiveFrom,101), convert(datetime,@effectiveTo,101))

    end

    If(@flag=2)

    begin

    Select convert(varchar,@effectiveFrom,103), convert(varchar,@effectiveTo,103) where TaxCode=@TaxCode

    end

    END

    I am impatiently waiting for your advice to correct any mistake in code to make the insert statement work fine ...Just to inform you whenprocedure is set to flag=2 it works fine but if flag is set to 1 then insert works only when date is mm/dd/yyyy else it says out of range error for the following date value 22/7/2012


    rajdey1

    Sunday, July 8, 2012 2:46 PM
  • The easiest way is to use variable like:

    begin
    	DECLARE @dateFrom AS datetime,@dateTo as datetime;
    	SET @dateFrom = (SELECT convert(datetime,@effectiveFrom,101));
    	SET @dateTo = (SELECT convert(datetime,@effectiveTo,101));
    	
    	Insert into taxmaster 
    		(TaxCode ,TaxName ,TaxPc,effectiveFrom, effectiveTo) 
    	values 
    		(@TaxCode,@TaxName ,@TaxPc , @dateFrom, @dateTo)
     
    end


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Naomi N Sunday, July 8, 2012 3:21 PM
    • Unmarked as answer by Naomi N Sunday, July 8, 2012 3:21 PM
    • Proposed as answer by Naomi N Sunday, July 8, 2012 3:21 PM
    • Marked as answer by amber zhang Monday, July 16, 2012 8:07 AM
    Sunday, July 8, 2012 2:57 PM
  • hi

    thanks for the advice i am back in office ... just to be sure i m using sqlserver 2005 hope it doesnt matter


    rajdey1

    Sunday, July 8, 2012 3:07 PM
  • this works with all version of SQL server

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by amber zhang Monday, July 16, 2012 8:07 AM
    Sunday, July 8, 2012 3:19 PM