.net / sqlite3 Date Types...

[zehd]

OK, so I get my date out of the database into a Date variable. I want to use that variable in a second query comparing that date to other dates in the database.

During debug, when I inspect the variable, the date format is different than the date format in the database. I think that's why the query fails...

When I worked with vb6, I just read everything as strings, and compared that way... But I know that if I use the date type I will also be able to add and subtract days and hours and time much easier than parsing the string into isolated date variables (year, month, date, etc...)

How should I be doing this?

[sub]

Something like this:

DbCommand findProgrammeCommand = dbProviderFactory.CreateCommand();
findProgrammeCommand.Connection = connection;
findProgrammeCommand.CommandText = "SELECT oid FROM PROGRAMME where start_time=?" , connection";
findProgrammeCommand.Parameters.Add(SystemConfiguration.getInstance().getDatabaseParameter("@start_date", System.Data.DbType.DateTime));

findProgrammeCommand.Parameters[0].Value = startTime;
findProgrammeCommand.Parameters[0].DbType = System.Data.DbType.DateTime;

[zehd]

This has raced ahead of what I've learned so far. I don't get this query syntax:
"SELECT oid FROM PROGRAMME where start_time=?" , connection";

Does this mean that the value retrieved from the database is formatted using a convention...?
...getDatabaseParameter("@start_d ate", System.Data.DbType.DateTime)


Or maybe in keeping with previous examples you've given me, the value for the data goes into a different structure, one that is preformatted...

findProgrammeCommand.Parameters[0].Value = startTime;
findProgrammeCommand.Parameters[0].DbType = System.Data.DbType.DateTime;

I know that in many things, there is more tan one way to skin a cat.

I just barely understand how to get data from a query into an array structure with predefined data types...

Code:

Public Class Recording_Schedule
        Public oid As Integer
        Public programme_oid As Integer
        Public capture_source_oid As Integer
        Public status As Integer
        Public filename As String
        Public recording_type As Integer
        Public recording_group As Integer
        Public manual_start_time As Date
        Public manual_end_time As Date
        Public manual_channel_oid As Integer
        Public quality_level As Integer
        Public pre_pad_minutes As Integer
        Public post_pad_minutes As Integer
        Public priority As Integer
        Public conversion_profile As String
    End Class


----

      SQL_connection.ConnectionString = "Data Source=" & GBPVRdb3 & ";Version=3;New=True;"
        SQL_connection.Open()

        Dim command As DbCommand = DbProviderFactory.CreateCommand()
        command.Connection = SQL_connection
        command.CommandText = "SELECT * from recording_schedule where status='0'"
        Dim dataReader As DbDataReader = command.ExecuteReader()
        r_slist = New ArrayList() 'r_s = Recording_Schedule

        While dataReader.Read() 'iterates all fileds
            Dim myR_S = New Recording_Schedule()
            myR_S.oid = dataReader.GetInt32(dataReader.GetOrdinal("oid"))
            myR_S.programme_oid = dataReader.GetInt32(dataReader.GetOrdinal("programme_oid"))
            myR_S.capture_source_oid = dataReader.GetInt32(dataReader.GetOrdinal("capture_source_oid"))
            myR_S.status = dataReader.GetInt32(dataReader.GetOrdinal("status"))
            myR_S.filename = dataReader.GetString(dataReader.GetOrdinal("filename"))
            myR_S.recording_type = dataReader.GetInt32(dataReader.GetOrdinal("recording_type"))
            myR_S.recording_group = dataReader.GetInt32(dataReader.GetOrdinal("recording_group"))
            myR_S.manual_start_time = dataReader.GetDateTime(dataReader.GetOrdinal("manual_start_time"))
            myR_S.manual_end_time = dataReader.GetDateTime(dataReader.GetOrdinal("manual_end_time"))
            myR_S.manual_channel_oid = dataReader.GetInt32(dataReader.GetOrdinal("manual_channel_oid"))
            myR_S.quality_level = dataReader.GetInt32(dataReader.GetOrdinal("quality_level"))
            myR_S.pre_pad_minutes = dataReader.GetInt32(dataReader.GetOrdinal("pre_pad_minutes"))
            myR_S.post_pad_minutes = dataReader.GetInt32(dataReader.GetOrdinal("post_pad_minutes"))
            myR_S.priority = dataReader.GetInt32(dataReader.GetOrdinal("priority"))
            If (Not Convert.IsDBNull(dataReader.GetValue(dataReader.GetOrdinal("conversion_profile")))) Then
                myR_S.conversion_profile = CStr(dataReader.GetString(dataReader.GetOrdinal("conversion_profile")))
            End If
            r_slist.Add(myR_S)
        End While
        dataReader.Close()
        SQL_connection.Close()

so then I get my date data in r_slist(i).manual_start_time (with i iterated through array...)

Again, that date format that is contained in this variable does not match in appearance to what I think I see when I inspect the database using a browser...

[sub]

This has raced ahead of what I've learned so far. I don't get this query syntax:
"SELECT oid FROM PROGRAMME where start_time=?" , connection";

Does this mean that the value retrieved from the database is formatted using a convention...?
...getDatabaseParameter("@start_d ate", System.Data.DbType.DateTime)

Sorry, I do things differently and tried to edit this back to the way you'd use it, but didnt do it quite right. Get rid of the ", connection" bit at the end of the string on the first line. The question mark is supposed to be there. Its a place holder for a parameter.

My GetDatabaseParameter function I referred to above is something like this:

Code:

public DbParameter getDatabaseParameter(string name, System.Data.DbType type)
        {
            DbParameter param = dbProviderFactory.CreateParameter();
            param.ParameterName = name;
            param.DbType = type;
            return param;
        }

[zehd]

Sorry, I do things differently and tried to edit this back to the way you'd use it, but didnt do it quite right. Get rid of the ", connection" bit at the end of the string on the first line. The question mark is supposed to be there. Its a place holder for a parameter.

My GetDatabaseParameter function I referred to above is something like this:

Code:

public DbParameter getDatabaseParameter(string name, System.Data.DbType type)
        {
            DbParameter param = dbProviderFactory.CreateParameter();
            param.ParameterName = name;
            param.DbType = type;
            return param;
        }

OK. Since I'm not processing 30000 records for this, I'm sure I'll be fine. What I did was retrieve the value into a string typed variable. When I want to compare, I load both variables I want to compare into a DateTime and do my diff or compare...

I haven't tried sending value into the DB yet I suspect that's where I'll have the trouble.

BTW, I notice that you have those extra zeroes. That's for UTC right? How to you work with that?