What options provide the default SQL Server date and time format?
Let's say I have a table like this:
Table name "DateTimeFormatCheck">
I used below query to insert:
Insert into [dbo].[DateTimeFormatCheck] ([DateTimeCheck]) values('11/19/2014 1:29 PM')
The date is inserted as 2014-11-19 13: 29: 00.000 but my insert format ( M / d / yyyy h: m tt ) is not the same.
Now I want to know how does SQL Server determine my string date format that I have provided? and why does it always provide this format yyyy-mm-dd hh: mm: ss after insert?
thank
source to share
Do not do this! Don't use strings for dates, and don't use a specific date or date / time format, this is a recipe for disaster.
SQL Server does not have a "date format", it just formats it to convert dates to and from strings. One is the default, controlled by the server setting. You can neither assume nor depend on a particular sort, so you should avoid conversions whenever possible. Moreover, passing a string value can prevent SQL Server from using indexes on date columns because it will have to convert the string to the column type below.
It is much easier and safer to pass dates as variables or parameters typed for date. You avoid all the conversion clutter in this way, from and from, and avoid SQL injection attacks.
There is no reason to pass strings instead of dates to the server. All SQL Server clients (including ADO.NET) allow parameterized queries to be executed. ORMs like NHibernate and Entity Framework also generate parameterized queries for date columns.
Whenever you need to create a string literal, use one of the invariant formats like 20140913
or2012-11-07T18:26:20
You can read more about this in Writing International T-SQL Statements
source to share
The format is sqlserver yyyy-mm-dd , but you can determine your input by running the command
set dateformat dmy - accept dmy format
set dateformat mdy
select cast( '11-9-2014' as date)
set dateformat dmy
select cast( '11-9-2014' as date)
updated
Ideally, you can't try changing the format, you can check your data and then paste.
Whenever we insert a datatype into datetime, sqlserver will implicitly try to convert to mmddyyy hhmmss format. so if you gave the date as 19/11/2014 it converts to 11/19/2014 means November 19, 2014.
But if you give more than 12 in the middle, it will not convert implicitly and throw a conversion error.
In addition to the mmddyyyy format, you must explicitly use a translate or transform function to allow data to be inserted or updated.
Before casting, you can use ISDATE or TRY_PRASE or PARSE function in sqlserver, whether it will check if the transition is possible or not.
you can create a function or just add a line like
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
source to share