Dealing with dates in SQL Server for ASP.NET developers

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.

string.Format("{0:dd/MM/yyyy}", DateTime.Now);

I hope this post will help those who will encounter problems with date formatting.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: