Datetime format issue in MSSQL server

I have an asp.net apllication with entity framework abs mssql server database. In my local mssql server the smalldatetime datatype column saving the date as yyyy-MM-dd format. But whike I am deploying in cloud it is saving as MM/dd/yyyy format and sometimes it is not saving also and giving string was not a valid datetime error. It causes a problem in date range
retrive beacause of different format in local database and cloud database. How to fix this problem please help me.
GRChandrashekarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
consider using the un-separated YYYYMMDD input format

Does you cloud provider allow you to set the default format to YYYY-MM-DD - or have you asked them how that might be achieved?

and note:
smalldatetime is NOT stored "in a format" - in fact it is stored as 2 integers and isn't stored in a "human readable" fashion at all.

A complication of this stems from needing to represent date/time as input and as output in human readable ways - so it is easy to confuse the visible format with the method of storage.

The safest way to handle date input is YYYYMMDD hh:mm:ss e.g.
select * from atable where adatefield >= '20130801' and adatefield < '20130901'

see: "The ultimate guide to the datetime datatypes"
Please pay particular attention to the section "Date and time formats for input" and note
The smalldatetime and datetime types are unfortunate since they are dependent on not only SET LANGUAGE but also SET DATEFORMAT (including the login's language setting).
also see: set dateformat http://technet.microsoft.com/en-us/library/ms189491(v=sql.105).aspx

Other references: "Beware of Between"
"Bad habits to kick : mis-handling date / range queries"
"What do BETWEEN and the devil have in common?"
0
Tony303Commented:
.
0
GRChandrashekarAuthor Commented:
Hi PortletPaul,
   I researched about it and I came to know that if I will change the mssql language settings then we can save what ever format we want. But if my application will shift to a different cloud server having different mssql server language settings then the same problem will happen. For this how to fix please help me.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.