CODEWITHSUNDEEP
sqlvb.net
Denver
Denver

Reputation: 147

VB.net Searching the date while removing the time in a Query

is this possible? i have like this.

16/01/17-12:20:58

inside my database and i want to search the date only. Is there a function inside the query like sub-string or is there any other option?

sql = "select * from qry where DateConduct between '" & dtFrom.Value.Date.ToString("yy-MM-dd") & "' and '" & dtTo.Value.Date.ToString("yy-MM-dd") & "'"

if possible i want something similar above. i'm using MS ACCESS as database.

Upvotes: 0

Views: 490

Answers (3)

eftpotrm
eftpotrm

Reputation: 2281

(Please parameterise your query. It'll run faster and it's more secure.)

Your post doesn't specify a particular database platform. If you're on a recent version of SQL Server you have the DATE as well as DATETIME type, so can do 

SELECT * FROM Qry WHERE CAST(DateConduct AS Date)=@DateParam

If you're on something older or different there's a number of techniques -

DATETIME is a FLOAT internally, so you can do CAST(TRUNCATE(CAST(DateVal AS Float)) AS DateTime) to get just the date component. Compare that with your date and you're good to go.

BETWEEN queries as you've seen can help. If you do

SELECT * FROM Tbl WHERE DateVAl BETWEEN @StartOfDay AND DATEADD(s,86399,@StartOFDay)

then you'll also get all the records for the day.

Upvotes: 1

Abner
Abner

Reputation: 426

You can cast the field as DATE or use the CONVERT function to compare only dates. I preffer CONVERT, always worked for me.

CAST.

sql = "select * from qry where CAST(DateConduct AS DATE) between '" & dtFrom.Value.Date.ToString("yyyy-MM-dd") & "' and '" & dtTo.Value.Date.ToString("yyyy-MM-dd") & "'"

CONVERT.

sql = "select * from qry where CONVERT(VARCHAR, DateConduct, 112) between '" & dtFrom.Value.Date.ToString("yyyyMMdd") & "' and '" & dtTo.Value.Date.ToString("yyyyMMdd") & "'"

Hope this helps. Best regards.

Upvotes: 0

Jim R
Jim R

Reputation: 94

You can use the SQL date() function to return just the date part

Upvotes: 0

Related Questions