Ok I know that this is very simple but I can't figure it out.

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

...to be more specific, If I were using Oracle I would say: ...Where trunc(media_date) = trunc(ldDate)

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

I am not 100% sure of a way to truncate the time from a date, but what I would do is this:

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

So should I have set up the table a different way?  Is there a Date that doesn't have the time attached to it in SQL Server?

Nope, all dates in SQL Server have the time attached to them.

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

If you are using the StrataFrame DateTime control, there is a property that is called StripTimeFromDate (or something similar to that )...anyway, this will remove the time from the date and will always be consistent when searching or selecting.  It is basically midnight and would look something like this: 12/01/2006 00:00:00 .  If you have a time in teh date column, the yes, this would be a problem when searching.  In this case you would have to take the time into account.

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

 

Another approach to stripping the time from a date is to do a double cast as shown below:

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

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

Regards,

Flavelle

On the BrowseDialog, set it to searching with a BeginsWith and see if that resolves your problem.  This should exclude the time.