0

I have the following scenario, when I run this statement in SQL Server 2014:

set dateformat ymd;
select cast('1315/06/31' as date )

'1315/06/31' is the persian calendar.

I've received this:

Error:Conversion failed when converting date and/or time from character string.

But if I run this:

set dateformat ymd;
select cast('1394/07/21' as date )

it will return the correct value.

Can someone please explain why I'm getting an error in the first case?

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
Nazanin
  • 13
  • 1
  • 1
  • 6
  • 4
    Doesn't June have only 30 days? – meteor Nov 11 '15 at 13:37
  • I have a feeling it's [something like this](http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server), but on a different calendar. – Radu Gheorghiu Nov 11 '15 at 13:37
  • It equal to 'September 22, 1936' – Nazanin Nov 11 '15 at 13:39
  • The SQL Server [`DATE` data type](https://msdn.microsoft.com/en-GB/library/bb630352.aspx) uses the Gregorian calendar, and in the Gregorian calendar June does not have 31 days, so the conversion failed. The second one works because July has a 21st Day. You would need to use an alternative data type to store dates from the Persian Calendar, or store your dates as per the Gregorian date and work convert as and when you need to (in the presentation layer usually, but [this article](http://raresql.com/tag/iranian-calendar-to-georgian-calendar-in-sql-server/) has some functions to do this). – GarethD Nov 11 '15 at 13:50
  • thank's Radu Yes your explanation is true. I have a varchar filed that contains persian and gerigorian dates with different formats. I want to make their formats as the same but on some datas I receive this error. do you have any suggestion for me that I can make all of their formats as the same? – Nazanin Nov 11 '15 at 14:18

0 Answers0