Avatar of GRChandrashekar
GRChandrashekarFlag for India asked on

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.
Microsoft ApplicationsMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon
PortletPaul

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?"
Tony303

.
ASKER
GRChandrashekar

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question