This morning I was having a problem in saving date type of data to sql server. The problem? If the current language of your instance is us_english then the format of the date is mdy which is month/date/year. Now my requirement was to save and display as dmy or date/month/year. When you try to save the format it will throw an error saying that you are trying to store an invalid date. How to deal with this? Follow the below steps:
1. Identify the language that is currently used by your login. How? Execute the below script in sql server.
select name, language from master.dbo.syslogins
2. If you can find your login there then identify the date format that is being used by your login’s language which you will know by issuing the below code in sql server.
select name ,alias, dateformat from sys.syslanguages
which retrieve the below list.
3. Then identify your requirement, in my case i need to use the British English format which is dmy or date/month/year. Now we have to change our login’s language by issuing the below code in sql server
SET Language British
4. We can now pass the dmy format to date fields in sql server by using the below code in asp.net.
I hope this post will help those who will encounter problems with date formatting.