I am using SQLExpress and I have a table called Library.  I have a BO call Library1.  I created a method called FillByDate.

The column on my table that I want to look at is media_date it is of DataType DateTime.

my SQL is: "Select * from library where Media_date = @ldDate"

 ldDate = cdate("10/15/2006")

I am also new to Sql Server so My Question is what date conversion function do I need to get my parameter and my Table.DateTime Field to work. 

PS, I don't know what the actual time would be...Just the Date

If I were using VFP I would use ttod(Media_date) = ctod('10/15/2006').

The Problem I am having is I don't get a record back.  Eventhough I know that date is there.

Below is my Method

Public Sub FillByDate(ByVal ldDate As Date)

Dim loCommand As New SqlCommand()

loCommand.CommandText = "Select * from Library where Media_date BETWEEN @ldMediaDate AND @ldMediaDatePlusOneDay"

loCommand.Parameters.Add("@ldMediaDate", SqlDbType.DateTime)

loCommand.Parameters("@ldMediaDate").Value = ldDate.Date

loCommand.Parameters.Add("@ldMediaDatePlusOneDay", SqlDbType.DateTime)

loCommand.Parameters("@ldMediaDatePlusOneDay").Value = ldDate.Date + New TimeSpan(1, 0, 0, 0)

Me.FillDataTable(loCommand)

End Sub

If you're setting the value in your default values, you can do this instead:

me.myfield = DateTime.Now.Date

Accessing the .Date property of a DateTime will return the date with the time truncated (alternatively, you could use the .TimeOfDay property to access just the time portion).

However, if you're setting the property in other ways, you can customize the property in the BOMapper to set the data within the business object to the .Date of the value passed.

This problem is similar to the issue I listed under enhancements to the BrowseDialog.  To select records by date, ignoring the time component on a SQL DateTime field you can do the following

Public Sub FillByDate(ByVal ldDate As Date)

   Dim loCommand As New SqlCommand

   loCommand.CommandText = "SELECT * FROM Library” _

                                              "WHERE CONVERT(Char(10),Media_date,101) = @SearchDate"

   loCommand.Parameters.Add("@SearchDate", SqlDbType.Char)

            loCommand.Parameters("@SearchDate").Value = string.Format("{0:MM/dd/yyyy}",ldDate)

   Me.FillDataTable(loCommand)

End Sub

-Larry

Using a SF maint form containing a SF textbox (Name) and SF DateTimePicker (DateTime) with StripTimeFromValue = True I added some records to a table.  All of the dates have a time of 12:00:00 AM.

If I use a BrowseDialog and try to select a date equal to, it fails to find the record. I don’t know if the DateTimePicker on the browse dialog is configured to StripTimeFromValue and that may be the issue. However in most cases my applications would have a meaningful time component to the DateTime field and I’d want to be able to search on just the date portion.

-Larry

cast(cast(@date as int) as datetime)

This was suggested to me on an MSDN forum and works very well.

Regards,

Flavelle

Trent,

BeginsWith is not an option for a field defined as datetime, it’s only available for text fields.  For datetime fields the options are =, >, <, >= or <=.

Flavelle,

Thanks for the suggestion.

-Larry

Any more suggestions on this? Should I assume that the browse dialog functions as designed and can’t find records with a date equal to just the date portion of a datetime field or is it something that will be fixed?

Thanks for the follow-up.  I hadn't heard anything and figured it had got lost in the mix.

-Larry