SQL Question (date match)

[omarswan]

Hi,
I'm writing a problem and I've come across this problem. I have a database table with some records. One of the field is a DateTime field. I want retieve the records that match a specific Date NOT the Date and Time, ONLY the DATE

the code below is what I have, but it doesn't work. How can I solve this?

tmpSql = "SELECT * FROM schedule WHERE expired_date = #" & Format(TheDate, "mm/dd/yyyy") & "#"

Note the variable TheDate is as Date - example "12/20/2000"

[davidrobin]

A little more information would help.

In what way does it not work? Are you getting a specific error or just not records sent back? Are you using ADO or DAO or the Data Environment?

[omarswan]

Hi,
no I'm not getting an error. It's just not returning the records. I thinks it's because the the datetime field has a time value also.

[HughLacey]

Hi,

It depends a bit on what database you're using but you probaly want to compare just the integer part of the date to your criteria.

In oracle this would be:

tmpSql = "SELECT * FROM schedule WHERE TRUNC(expired_date) = TO_DATE(" & Format(TheDate, "mm/dd/yyyy") & ", "MM/DD/YYYY")"

In Access/SQLServer:

tmpSql = "SELECT * FROM schedule WHERE int(expired_date) = #" & Format(TheDate, "mm/dd/yyyy") & "#"

[davidrobin]

I have tried to imitate your situation as best I could, there seems to be some rule on data types. When using the DataEnvironment I got an error telling me the # was an invalid character when the open method tried to execute. Hope this helps on some way.

Private Sub Command1_Click()
Dim tmpSQL  As String
Dim TheDate As Date
TheDate = #9/19/2000#

tmpSQL = "SELECT * FROM stmaos WHERE start_date = '" & Format(TheDate, "dd/mm/yyyy") & "'"

DE.rsc1.Open tmpSQL, DE.cn, adOpenForwardOnly, adLockReadOnly

MsgBox DE.rsc1.RecordCount

DE.rsc1.Close

End Sub

[omarswan]

Hi,
this is what I think is causing the problem.

comparing a value such as

11/23/00 12:33:15 AM and 11/23/00
start_date and TheDate



It's not returning the right values because TheDate does not have a time value that matches. All I want to do is match the two dates not the time.




[Edited by omarswan on 11-02-2000 at 06:54 AM]

[davidrobin]

What database are you using?

[davidrobin]

I managed to find this in MSDN

To search for an exact match on both date and time, use an equal sign (=). Microsoft SQL Server returns date and time values exactly matching the month, day, and year and at the precise time of 12:00:00:000 AM (default).

To search for a partial date or time value, use the LIKE operator. SQL Server first converts the dates to datetime format and then to varchar. Because the standard display formats do not include seconds or milliseconds, you cannot search for them with LIKE and a matching pattern, unless you use the CONVERT function with the style parameter set to 9 or 109. For more information about searching for partial dates or times, see LIKE.

I connected to an Access database and managed to get the folowing to work.

Private Sub Command1_Click()
Dim tmpSQL  As String
Dim TheDate As Date
TheDate = CDate("10/10/2000")
tmpSQL = "SELECT dt FROM calltype WHERE dt Like '%" & Format(TheDate, "dd/mm/yyyy") & "%'"

DE.rsc1.Open tmpSQL, DE.cn, adOpenForwardOnly, adLockReadOnly

MsgBox DE.rsc1.RecordCount

DE.rsc1.Close

End Sub

TTFN