c# - Sql query to search database by date -

c# - Sql query to search database by date -

can advice me sql query search ms database specific date.

for illustration want search transaction in day 13/02/2013. in database have 1 column called purchased date stores date of purchase.

in database date stored in format 16/02/2013 02:47:36 am.

i want tom come in date want in text box , pass value query.

public dataset orderbydate(datetime date) { // string connstring = "provider=microsoft.ace.oledb.12.0;data source=c:\\users\\amrit\\desktop\\database.accdb ;persist security info=false;"; dataset dataset = new dataset(); oledbconnection oleconn = new oledbconnection(connstring); seek { oleconn.open(); string sql = "select customer.[title] + space(2) + customer.[customer's name] customername, customer.[customer's ebayname], customer.[email address], customer.[phone number], customer.[address 1] + space(2) +customer.[address 2] + space(2) + customer.[city] + space(2) + customer.[post code]+ space(2) + customer.[country] address, customer.[item purchased], customer.[purchased date], customer.[total price] client [purchased date]='" + date; oledbdataadapter dataadapter = new oledbdataadapter(sql, oleconn); dataadapter.fill(dataset, "customer"); } grab (exception ex) { console.writeline(ex.tostring()); } { oleconn.close(); } if (dataset.tables.count <= 0) homecoming null; else homecoming dataset; }

always seek utilize original/proper info type queries. in case - datetime. never concatenate parameters sql - strings, if string user input.

the below should work:

public dataset orderbydate(datetime date) { string connstring = "provider=microsoft.ace.oledb.12.0;data source=c:\\users\\amrit\\desktop\\database.accdb ;persist security info=false;"; var dataset = new dataset(); using(var oleconn = new oledbconnection(connstring)) { seek { oleconn.open(); var cmd = oleconn.createcommand(); cmd.commandtext = "select * client [purchased date] between :datestart , :dateend"; cmd.parameters.addrange(new[] { new oledbparameter("datestart", date.date), new oledbparameter("dateend", date.date.adddays(1).addticks(-1)) } ); var dataadapter = new oledbdataadapter(cmd); dataadapter.fill(dataset, "customer"); } grab (exception ex) { console.writeline(ex.tostring()); } } homecoming dataset.tables.count <= 0 ? null : dataset; }

i removed long list of columns clarity. far understand, oracle expects parameters colon(:) in front end of parameter name opposed sql server's '@'. if above doesn't work seek @ or maybe utilize ? instead of parameter names , supply parameters without names.

the "using" part should handle closing connection fine if exception thrown, , looks tidier section in code.

regarding part:

new oledbparameter("datestart", date.date), new oledbparameter("dateend", date.date.adddays(1).addticks(-1))

date.date returns date part (time 00:00:00) , date.date.adddays(1) next date (time 00:00:00) illustration 2012-02-16 00:00:00 , 2012-02-17 00:00:00 - 24hrs- 1 day. can subtract 1 tick if like, thought still same. way selecting records date falls in range (hence using between). seek accomplish same using oracle datetime functions, shorter/cleaner believe.

c# sql datetime