Datetime format issue in MSSQL server

I have an 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.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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

Other references: "Beware of Between"
"Bad habits to kick : mis-handling date / range queries"
"What do BETWEEN and the devil have in common?"
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.