Solved

Datetimepicker records filter

Posted on 2009-05-12
5
257 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
Comment Utility
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
Comment Utility
Incorrect syntax error!
0
 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now