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]
Avatar of PortletPaul
Flag of Australia image

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?"
Avatar of Tony303
Flag of New Zealand image

Avatar of 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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server 2008
Microsoft SQL Server 2008

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo