33

I have the following piece of inline SQL that I run from a C# windows service:

UPDATE table_name SET 
    status_cd = '2', 
    sdate = CAST('03/28/2011 18:03:40' AS DATETIME), 
    bat_id = '33acff9b-e2b4-410e-baaf-417656e3c255', 
    cnt = 1, 
    attempt_date = CAST('03/28/2011 18:03:40' AS DATETIME) 
WHERE id = '1855'

When I run this against a SQL Server database from within the application, I get the following error:

System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

But if I take the piece of SQL and run it from SQL Management Studio, it will run without issue.

Any ideas what may be causing this issue?

amateur
  • 43,371
  • 65
  • 192
  • 320
  • 1
    For future readers: This error can also occur if a date is entered from before the earliest date allowed: 1st of January 1753. – A Jar of Clay Sep 10 '19 at 17:18

8 Answers8

66

Ambiguous date formats are interpreted according to the language of the login. This works

set dateformat mdy

select CAST('03/28/2011 18:03:40' AS DATETIME)

This doesn't

set dateformat dmy

select CAST('03/28/2011 18:03:40' AS DATETIME)

If you use parameterised queries with the correct datatype you avoid these issues. You can also use the unambiguous "unseparated" format yyyyMMdd hh:mm:ss

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 3
    Yes, that's a likely cause. The fix would be to use an unambiguous date format, e.g. year-month-day: `'2011-03-28 18:03:40'` – Joe White Mar 28 '11 at 23:15
  • I use the following to convert -> date.Value.ToString("MM/dd/yyyy HH:MM:ss") which would give me '03/28/2011 18:03:40'. What do i need to update in my application or database? – amateur Mar 28 '11 at 23:22
  • 1
    @Niall - If that's your format string you are also passing minutes in as months or vice-versa. – Martin Smith Mar 28 '11 at 23:28
  • @Joe what makes you think that works? `set dateformat dmy select cast('2011-03-28 18:03:40' as datetime)` - *`resulted in an out-of-range datetime value`* – RichardTheKiwi Mar 28 '11 at 23:31
  • 1
    @Richard - They are both 03. Is that just a coincidence? It wouldn't need to be `yyyyMMdd HH:mm:ss`? I whole heartedly agree on the parameters. Was looking to see if there was a built in string format of any use... – Martin Smith Mar 28 '11 at 23:43
  • @Martin - you're right. I was thinking {some other language}. C# => M-Month, m-minute. There is a standard format "s" but it's easier to build a custom format – RichardTheKiwi Mar 28 '11 at 23:47
  • 2
    @Martin - thanks for the login in user suggestion, that was it. – amateur Mar 29 '11 at 00:28
  • `yyyymmdd hh:mm:ss` should become `yyyyMMdd hh:mm:ss` otherwise `yyymmdd` will be interpreted as `year/minutes/day`. – DGibbs Apr 22 '16 at 11:29
  • @DGibbs in .net not TSQL. – Martin Smith Apr 22 '16 at 11:30
  • @MartinSmith Ahh, my mistake – DGibbs Apr 22 '16 at 11:30
  • Although that is just prose rather than TSQL code anyway and it might be clearer to people accustomed to .net format strings on reflection, – Martin Smith Apr 22 '16 at 11:33
  • If someone, like me, comes to this question wondering why C# datetimes break when transported to the server: Maybe you're converting them yourselves, and you don't realize that `myDate.toString("yyyyMMdd HH:mm:ss")` may _convert the colon_ depending on locale; you probably want `myDate.toString("yyyyMMdd HH':'mm':'ss")`. – Søren Debois Oct 14 '22 at 12:39
14

But if i take the piece of sql and run it from sql management studio, it will run without issue.

If you are at liberty to, change the service account to your own login, which would inherit your language/regional perferences.

The real crux of the issue is:

I use the following to convert -> date.Value.ToString("MM/dd/yyyy HH:mm:ss")

Please start using parameterized queries so that you won't encounter these issues in the future. It is also more robust, predictable and best practice.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 4
    it was the user account!!! The account the app was running under had a default language set to British English but when I logged in myself to sql server man studio my account was defaulted to English! – amateur Mar 29 '11 at 00:11
  • 1
    @Niall - Martin knew that as well, as prefaced in his answer. I just.. pointed out how to make the service work using code as-is. Thanks – RichardTheKiwi Mar 29 '11 at 00:12
  • 2
    +1 Parameterised queries make this much more straightforward! – Martin Smith Mar 29 '11 at 00:13
  • I think that `HH:MM` should be `HH:mm` there, rather than using the month number in the time portion! – Toby Speight May 05 '17 at 13:36
7

I think the best way to work with dates between C# and SQL is, of course, use parametrized queries, and always work with DateTime objects on C# and the ToString() formating options it provides.

You better execute set datetime <format> (here you have the set dateformat explanation on MSDN) before working with dates on SQL Server so you don't get in trouble, like for example set datetime ymd. You only need to do it once per connection because it mantains the format while open, so a good practice would be to do it just after openning the connection to the database.
Then, you can always work with 'yyyy-MM-dd HH:mm:ss:ffff' formats.

To pass the DateTime object to your parametrized query you can use DateTime.ToString('yyyy-MM-dd HH:mm:ss:ffff').

For parsing weird formatted dates on C# you can use DateTime.ParseExact() method, where you have the option to specify exactly what the input format is: DateTime.ParseExact(<some date string>, 'dd/MM-yyyy',CultureInfo.InvariantCulture). Here you have the DateTime.ParseExact() explanation on MSDN)

Iván Sainz
  • 409
  • 6
  • 12
1

I know that this solution is a little different from the OP's case, but as you may have been redirected here from searching on google the title of this question, as I did, maybe you're facing the same problem I had.
Sometimes you get this error because your date time is not valid, i.e. your date (in string format) points to a day which exceeds the number of days of that month! e.g.: CONVERT(Datetime, '2015-06-31') caused me this error, while I was converting a statement from MySql (which didn't argue! and makes the error really harder to catch) to SQL Server.

Mohsen Kamrani
  • 7,177
  • 5
  • 42
  • 66
1

It's a date format issue. In Ireland the standard date format for the 28th of March would be "28-03-2011", whereas "03/28/2011" is the standard for the USA (among many others).

srgerg
  • 18,719
  • 4
  • 57
  • 39
0

You could use next function to initialize your DateTime variable: DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Serhii Lisovyi
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 27 '21 at 12:55
-1

JAVA8: Use LocalDateTime.now().toString()

ariabele
  • 355
  • 1
  • 4
  • 9
-1

i faced this issue where i was using SQL it is different from MYSQL the solution was puting in this format: =date('m-d-y h:m:s'); rather than =date('y-m-d h:m:s');

Evans Cherono
  • 1
  • 4