Which settings provide SQL Server default datetime format?

Question

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')

It inserted date like 2014-11-19 13:29:00.000 , but my insertion format(M/d/yyyy h:m tt) is not same as inserted date.

Now i want to know How SQL Server detect my string date format that i have provided ? and Why it always provide this format yyyy-MM-dd HH:mm:ss after insertion?

Thanks

Answer 1

Don't do that! Don't use strings instead of dates and don't use a culture-specific date or datetime format, it's a recipe for disaster.

SQL Server doesn't have a "date format", just formats it uses to convert dates to and from strings. One of them is the default, controlled by the server's collation. You can neither assume nor should you depend on a specific collation, so you should avoid conversions whenever possible. Moreover, passing a string value can prevent SQL Server from using indexes on date columns because it would have to convert the string to the underlying column type.

It's far easier and safer to pass dates as date-typed variables or parameters. You avoid the entire conversion mess this way, to and from and avoid SQL injection attacks.

There's no reason to pass strings instead of dates to the server. All SQL Server clients (including ADO.NET) allow you to execute parameterized queries. ORMs like NHibernate and Entity Framework also generate parameterized queries for date-typed columns.

Whenever you need to create a string literal, use one of the invariant formats like 20140913 or 2012-11-07T18:26:20

You can read more about it at Writing International T-SQL Statements

Answer 2

The format of sqlserver is yyyy-mm-dd, but you can define your input by execute the command

set dateformat dmy -- to 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 not try to change format, you can validate your data and then insert.

Whenever we insert into datetime datatype, sqlserver will implicitly try to convert into mmddyyy hhmmss format. so if you given date as 19/11/2014 , it convert into 11/19/2014 means 19th nov 2014.

But if you give more than 12 in the middle portion, it will not convert implicitly and throw the error of conversion.

Other than mmddyyyy format, you must use to cast or convert function explicitly to allow the data insert or update.

Before the casting you can use ISDATE or TRY_PRASE or PARSE function in sqlserver, will check to conversion possible or not.

you can create a function or just add line as

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