How To Insert Any Format of dateTime into sql database using asp.net

Question

Here I am Giving The Queries What I am using:

1.INSERT into sample values(convert(varchar,'19-11-2014 10:10:41',103))

2.INSERT into sample values (convert(varchar,'2014-11-19 10:10:41',103))

3.INSERT into sample values(convert(varchar,'11-19-2014 10:10:41',103))

The database Format is:yyyy/mm/dd HH:mm:ss:mmm

In Above Queries First one Throwing Error and Remaining Two Queries Working Fine.so How to insert Any Datetime Format into Sql No change in Query.

Please Reply As Early As Possible,Thank You.

What date is "02-03-2015"? The second of march, or february the third? You can't just accept both formats and expect "the system" to figure out the correct date. — Hans Keﬆing
– Hans Keﬆing, Commented Nov 19, 2014 at 10:11

Rajesh · Accepted Answer · 2014-11-19 07:23:17Z

1

Since you said using ASP.Net then why are you using plain SQL queries? Use parameters instead.

Lets say you have a date in string format 19/11/2014 and you need to insert into db so the correct way will be first convert the string into a date like

DateTime date= DateTime.ParseExact("19/11/2014 00:00:00","dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);

now use parametrised query to insert see details-->http://www.aspsnippets.com/Articles/ASPNet-SqlDataSource-pass-value-to-SelectParameter-using-QueryString-Parameter-Example.aspx

Using parameters will serve you two main purpose

You will be safe from SQL Injection attack
the problem that you are currently facing with format of DateTime will go away since the serialization will be done by ASP.Net.

edited Nov 19, 2014 at 7:23

Rajesh

1,6305 gold badges36 silver badges63 bronze badges

answered Nov 19, 2014 at 5:56

Ratna

2,3293 gold badges26 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajay2707 · Accepted Answer · 2014-11-19 09:50:59Z

0

this is very general , but complicated situation to get the input date which in proper format or not. Which ultimately gives an error at run-time , even in the production server we get the error and we rectify the issue hour and hour.

Ultimately sqlserver introduce the new function called "ISDATE" or TRY_PARSE-only sql2012 or above, which return the 1 if input data is date format.

declare @dTable table ( datecolumn datetime)

INSERT into @dTable values (case isdate('19-11-2014 10:10:41') when 1 then '19-11-2014 10:10:41' else null end )

INSERT into @dTable values ('2014-11-19 10:10:41')

INSERT into @dTable values ('11-19-2014 10:10:41')

select * from @dTable

If still you want , this is not right way, then you can create a function of dateformat, in which you can give string datevalue in any format and function give the data date or null return.

check this links.

http://www.codeproject.com/Articles/576178/cast-convert-format-try-parse-date-and-time-sql#4

create function convertStringIntoDate      
(
    @stringValue varchar(50)
)
RETURNS datetime
AS          
BEGIN          
    DECLARE @datereturn datetime          

    set @datereturn = TRY_PARSE( @stringValue)


RETURN @datereturn 
END

or you can check this logic too.

declare @dt varchar(50) = '19-11-2014 10:10:41'

declare @dTable table ( datecolumn datetime)

INSERT into @dTable values (
case 
    when isdate(CONVERT( varchar(50), @dt)) = 1 then CONVERT( varchar(50), @dt) --'19-11-2014 10:10:41' 
    when isdate(CONVERT( varchar(50), @dt, 103) ) = 1 then CONVERT( datetime, @dt , 103 )  --'19-11-2014 10:10:41'  
    when isdate(CONVERT( varchar(50), @dt, 102) ) = 1 then CONVERT( datetime, @dt , 102 )  --'19-11-2014 10:10:41'  
    --when --give other format as above given and if not set in any dateformat , then simply return null
    else  
        null
end )


select * from @dTable

edited Nov 19, 2014 at 9:50

answered Nov 19, 2014 at 6:58

Ajay2707

5,8286 gold badges44 silver badges61 bronze badges

3 Comments

Techbee Over a year ago

Thanks Ajay For responce but i want to return Date and so i can insert in DB I dont want any null values.

Ajay2707 Over a year ago

yes, for that you have to explicitly create a function or procedure which return the proper value, not throw error. please check link which I shared

Techbee Over a year ago

Ya Got it I will Use Like This Thanks Ajay2707 For Responce.

Pரதீப் · Accepted Answer · 2014-11-19 05:53:57Z

0

Try this.

INSERT into sample values (convert(datetime,'19-11-2014 10:10:41',103))

INSERT into sample values (convert(datetime,'2014-11-19 10:10:41',102))

INSERT into sample values (convert(datetime,'11-19-2014 10:10:41',102))

answered Nov 19, 2014 at 5:53

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

1 Comment

Techbee Over a year ago

Thanks For Response But the first Query is having 103 code and remaining having 102 but i dont want to change later in coding please get back to me with all formats should execute

Collectives™ on Stack Overflow

How To Insert Any Format of dateTime into sql database using asp.net

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related