codingdir logo sitemap sitemap |

Sending a DateTime to a SQL Query in C#

By : , Category : c#

Your problem is in this line:

private static void _loadSqlCommand(List<string> args, object[] values, SqlCommand cm)
    for (int i = 0; i < args.Count; i++)
        // This line:
        cm.Parameters.AddWithValue(args[i], values[i].ToString());

Your .ToString() call forces .NET to tell SQL Server that the parameter you're sending in is a String type and SQL is unable to convert the string (nvarchar) to datetime directly. If you remove the .ToString() call, it should work as expected.

private static void _loadSqlCommand(List<string> args, object[] values, SqlCommand cm)
    for (int i = 0; i < args.Count; i++)
        // This should work:
        cm.Parameters.AddWithValue(args[i], values[i]);
ReLated :

That's simply because you can't add times. You can add time deltas, but not times themselves.

I mean, what is 3rd Aug 1995 + 19th June 454? It doesn't make sense. You can add the distance from Year 0 but that's different to adding the dates themselves.

What do you hope to get from this calculation?

If you hope to find the mean (why?), you'll have to use a less direct route

import datetime

d = [datetime.datetime(2013, 5, 1, 9, 31, 24), datetime.datetime(2013, 6, 11, 17, 22, 18), datetime.datetime(2013, 4, 3, 16, 6, 59)]

d[0] + sum((d_i-d[0] for d_i in d), datetime.timedelta(0)) / len(d)
#>>> datetime.datetime(2013, 5, 5, 22, 20, 13, 666667)

This finds the sum of offsets from d[0], means those and then adds that offset back on.

I think you need to have a group function.

SELECT DISTINCT `users`.id FROM `users` 
LEFT OUTER JOIN `events` ON events.user_id =
LEFT OUTER JOIN `purchases` ON `purchases`.id = `events`.purchase_id
WHERE events.starts_on <= '2013-07-15' or events.starts_on >= '2013-08-12'
group by `users`.id
having min(starts_on) <= '2013-07-15' and max(starts_on) < '2013-08-12'
LIMIT 0, 30;

You asked:

is there a way to add a trigger for date,datetime fields to return in UK format in php or mysql.


The MySQL internal datetime format is not changeable. The only locale-sensitive setting in the DBMS is the language in which the names of months and weekdays are rendered. In the US, we got those names from England. So that doesn't help.

If you want MySQL to render dates from its tables in Euro format (25.12.2015) you need this.

     DATE_FORMAT(datevalue, '%e.%c.%Y')

If you have the dates in php, you can use this sort of thing.

    date('j.n.Y', $datevalue);

Many php applications offer a global $DATEFORMAT string constant, that can be changed for use in a different locale. You may want to adapt your code to do this. For example, the WordPress team have sorted this out very nicely indeed.

I have solved this problem. It was due to the fact that the Oracle server that the query was being passed to through openquery wanted the date specified in it's format. This was solved with this change to the expresion for the parameter:

=year(Parameters!AsAt.Value) & "-" & Right("00" & Month(Parameters!AsAt.Value), 2) & "-" & Right("00" & Day(Parameters!AsAt.Value), 2) & " " & Right("00" & Hour(Parameters!AsAt.Value), 2) & ":" & Right("00" & Minute(Parameters!AsAt.Value), 2)

Message :
Login to Add Your Comments .
How to disable registered OpenCL platforms on Windows?
Is Observable broken in Angular 2 Beta 3?
Cross-thread operation not valid when using Invoke
How to pass an IEnumerable or queryable list of properties from Controller to View
Finding numbers after a certain keyword using Python
Pocketsphinx recognizes random phrases in a silence
Passing non-thread-safe objects through thread-safe containers
React scroll nav
BizTalk WCF-BasicHttp Adapter does not allow Empty string for Service Certificate Props
Why property ''cause" of Exception is repeating forever?
Privacy Policy 2017 © All Rights Reserved .