SQL Insert Query

1

I need to convert 17/08/2016 02:37:00 to 2016-08-17 02:37:00

2

Try this

select CONVERT(VARCHAR(20),convert(datetime, replace('17/08/2016 02:37:00', '/', '-'), 103), 20)

3

thanks, for immediate response, but in my design screen asp.net, I have given the date format as below
$("#txtbddatestart").datepicker({ dateFormat: 'dd/mm/yyyy HH:mm:ss' });
});
and in asp.net program in page load i mentioned the date string as
txtbddatestart.Text = DateTime.Now.ToString("dd/MM/yyyy, HH:mm:ss")

I need to insert in Sql data base as 'yyyy-mm/dd hh:mm:ss'

Thanks in advance

4

If you insert the value into a DATETIME column it is stored however SQL wants it to be. If you are inserting it into a character datatype, please reconsider.

5

If you insert into SQL as a string in the format 'yyyymmdd hh:mm:ss' then SQL will be happy (i.e. SQL will make an implicit and unambiguous conversion to Date / DateTime datatype). Any other arrangement of Year/Month/Day and punctuation is treated as being ambiguous and SQL will convert the date based on format-specification hints that you may provide, otherwise what Locale the server is set to, plus what Language etc. the current session connected with, and so on. Ambiguous date conversion is a minefield - I suggest you stick to using the UNambiguous format.

Alternative is to use a DateTime datatype in your asp.net APP to store the date, and then pass that (i.e. as a query parameter) to SQL, and then the date-time will travel in a native binary format between the two.

1 Like