Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

date format while inserting irrespective of regional settings Expand / Collapse
Posted Thursday, May 1, 2014 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 5:04 AM
Points: 6, Visits: 14
as per my understanding If I pass a date to sql server to insert it will parse the date on the basis of the regional settings of the machine where sql-server is installed. For example if I pass a date as "10/01/2000" and the regional setting is "dd/MM/yyyy" then sql-server will insert the date as "10th January 2000" and if the regional setting is "MM/dd/yyyy" then "1st October 2000" will be inserted. Am I right?....Please bear with me as I am not proficient in SQL-SERVER....if this is the case then prior to writing code I have to be confirmed about sql-server machine regional settings......Is there a way that I can send SQL-SERVER the format also?
Post #1566642
Posted Thursday, May 1, 2014 4:12 AM



Group: General Forum Members
Last Login: Wednesday, February 10, 2016 11:50 AM
Points: 6,897, Visits: 13,559
It doesn't depend on the SQL Server setting but on the setting of the date format setting at the point the INSERT statement.
It is influenced by the following settings:
The language setting of the user running the command / batch / sproc
The server setting

If you want to avoid dealing with different settings for date format, I'd recommend to use the ISO standard: YYYMMDD HH:MM:SS.
So, instead of sending "10/01/2000" you should either use "20000110" or "20001001", depending on the date it refers to.
This will always convert to the same date, regardless of the settings mentioned above.

A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1566663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse