0

Currently am using below mentioned code

CONVERT(DATETIME, "16-05-2015 21:27:25.280",105)

Using this its working in my system culture and one of our client in UK. But facing issue in our US client.

Error message:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

So there any way to convert the string to DATETIME depends on system culture like in .net.

Thanks in advance

Thejus T V

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
thejustv
  • 2,009
  • 26
  • 42

1 Answers1

2

The various settings (language, date format) only influence how the DateTime is shown to you in SQL Server Management Studio - or how it is parsed when you attempt to convert a string to a DateTime.

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • `YYYYMMDD` is **not** an ISO format. The ISO format for just a date is `YYYY-MM-DD` –  May 16 '15 at 16:22
  • @a_horse_with_no_name: yes - theoretically, you're right - but `YYYY-MM-DD` is ***NOT*** language independent in SQL Server - just try it `SET LANGUAGE german SELECT CAST('2015-05-25' AS DATETIME)` - this causes an **error**! But `SET LANGUAGE german SELECT CAST('20150525' AS DATETIME)` works as expected (this applies **only** to the `DATETIME` datatype - with `DATE` or `DATETIME2(n)`, your ISO format works just fine) – marc_s May 16 '15 at 16:58
  • @marc_s In my situation the input dateTime vary with system date format. Sometimes it may dd-mm-yyyyy , sometimes it may mm-dd-yyyy. – thejustv May 18 '15 at 08:58
  • @marc_s Excuse me sir, is there any way we could contact you directly? My email address is available on my profile page. Thank you! – Zam Jun 10 '15 at 09:17