CODEWITHSUNDEEP
c#sql-serverdatetime
Erre Efe
Erre Efe

Reputation: 15557

SQL Date cast between different framework languages

I have this query:

SqlCommand cmdImpRN = new SqlCommand("SELECT COUNT(*) FROM copies WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, DATE_TIME_COLUMN)) = @Date;", conn);
cmdImpRN.Parameters.AddWithValue("@Date", DateTime.Now.ToString("MM-dd-yyyy"));
n_i_rn = (int)cmdImpRN.ExecuteScalar();

This query is intented to return the number of tuples of copies whose DATE_TIME_COLUMN date (not time) is equal to today date. I'm currently deploying to diverse .NET framework local environments so I'm ensuring that the @Date is added as "MM-dd-yyyy" and it was working just fine until some client buy and install SQL Server on spanish. Now the app doesn't work on that throwing an exception like:

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

It seems I can make it work doing this:

cmdImpRN.Parameters.Add(new SqlParameter("@Date", SqlDbType.Date){Value = DateTime.Now })

But I'm not sure if this is the way. Why? Because I don't know if DateTime.Now returns different for different .NET Framework languages installed and because I don't know if that constructor will create a valid Date Object no matter if the return of DateTime.Now is in MM-dd-yyyy format or dd-MM-yyyy format.

Any advice? Thanks in advance.

Upvotes: 0

Views: 155

Answers (2)

Chris Shain
Chris Shain

Reputation: 51329

Your use of just DateTime.Now is correct. DateTime.Now always returns the correct date for the end-user's configured timezone. What happens is that the date is sent to the server in binary format (which is not locale-dependent), rather than as a string, and the server does the comparison against that binary format directly, avoiding any locale-specific date formatting issues.

Upvotes: 2

Nikki9696
Nikki9696

Reputation: 6348

Use DateTime.Now.Date. It's already a DateTime and format mm/dd/yyyy won't matter. Better yet, get used to UTC dates and times so you don't need to worry about user local time, server local time, and what have you.

The Date property has no time portion per se (it's set to midnight).

http://msdn.microsoft.com/en-us/library/system.datetime.date.aspx

Upvotes: 1

Related Questions