?
Solved

Datetimepicker records filter

Posted on 2009-05-12
5
Medium Priority
?
275 Views
Last Modified: 2012-05-06
Hello,

I have a DateTimePicker on a form. The DateTimePicker is formated as Short date, so the result of dtpReport .Text is something like 12-05-2009.
I have a SQL table with a field date (smalldate format) with values like 13-05-2009 7:52:32.
I have the following SQL statment to filter my records:

SQL = "SELECT coord.coord, DefectsReg.data, DefectsTipo.tipo, DefectsReg.lado, kenn.modelo FROM coord INNER JOIN DefectsReg ON coord.defeito = DefectsReg.id INNER JOIN DefectsTipo ON DefectsReg.type = DefectsTipo.id INNER JOIN kenn ON DefectsReg.kenn = kenn.id WHERE (DefectsReg.data >= CONVERT(DATETIME, '" & dtpDataRelatorio.Text & "', 102))"

wich is giving me this result:

SELECT coord.coord, DefectsReg.data, DefectsTipo.tipo, DefectsReg.lado, kenn.modelo FROM coord INNER JOIN DefectsReg ON coord.defeito = DefectsReg.id INNER JOIN DefectsTipo ON DefectsReg.type = DefectsTipo.id INNER JOIN kenn ON DefectsReg.kenn = kenn.id WHERE (DefectsReg.data >= CONVERT(DATETIME, '12-05-2009', 102))

but now records are been displayed. Can someone help me please?

Thanks,

jppinto
0
Comment
Question by:jppinto
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24372208
If the time factor is not important then try using this:
CONVERT(DATETIME, dtpDataRelatorio
.Value.ToString("yyyy-MM-dd 23:59:59")
0
 
LVL 33

Author Comment

by:jppinto
ID: 24372553
Incorrect syntax error!
0
 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 2000 total points
ID: 24372883
Try now using either of the options mentioned in snippet below:

'OPTION 1
SQL = "SELECT coord.coord, DefectsReg.data, DefectsTipo.tipo, DefectsReg.lado, kenn.modelo FROM coord INNER JOIN DefectsReg ON coord.defeito = DefectsReg.id INNER JOIN DefectsTipo ON DefectsReg.type = DefectsTipo.id INNER JOIN kenn ON DefectsReg.kenn = kenn.id WHERE (DefectsReg.data >= CONVERT(DATETIME, '" & dtpDataRelatorio.Value.ToString("yyyy-MM-dd 23:59:59") & "')"
 
'OPTION 2
SQL = "SELECT coord.coord, DefectsReg.data, DefectsTipo.tipo, DefectsReg.lado, kenn.modelo FROM coord INNER JOIN DefectsReg ON coord.defeito = DefectsReg.id INNER JOIN DefectsTipo ON DefectsReg.type = DefectsTipo.id INNER JOIN kenn ON DefectsReg.kenn = kenn.id WHERE (DefectsReg.data >= CONVERT(DATETIME, '" & dtpDataRelatorio.Value.ToString("yyyy-MM-dd 23:59:59") & "', 102)"

Open in new window

0
 
LVL 33

Author Comment

by:jppinto
ID: 24373123
The first solution worked. I've changed the 23:59:59 to 00:00:00 to get the records from the complete day.

Thanks,

jppinto
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24373385
That's good if it worked for you. Just remember when you wish to compare two dates, follow the same but put 00:00:00 in Start Date and 23:59:59 in the End Date.
Happy programming.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question