Why does this SQL return 1/1/1914 for the MIN/oldest date?

Question

0.00/5 (No votes)

See more:

I have data which, when queried via LINQPad, gives me "2008-04-25 10:38:56" for this query:

C#

SELECT MIN(dateTimeTaken) FROM PhotraxBaseData

I get "2014-04-27 19:26:09" for "SELECT MAX(..."

Yet, when I run this code:

C#

dateFrom.Date = PhotraxSQLiteUtils.GetEarliestDate();
dateTo.Date = PhotraxSQLiteUtils.GetLatestDate();
 . . .
internal static DateTimeOffset GetEarliestDate()
{
    DateTimeOffset dto;
    using (var db = new SQLite.SQLiteConnection(App.DBPath))
    {
        string sql = "SELECT MIN(dateTimeTaken) FROM PhotraxBaseData";
        dto = db.ExecuteScalar<DateTimeOffset>(sql);
    }
    return dto;
}

Note: GetLatestDate() is identical except for using MAX insted of MIN

...in both cases, what is actually returned from the methods is "1/1/0001" and what is displayed in the data controls is "1/1/1914". The data controls are declared this way:

HTML

<StackPanel Orientation="Horizontal">
    <TextBlock Text="Photos taken between">
    </TextBlock>
    <DatePicker x:Name="dateFrom">
    </DatePicker>
</StackPanel>
<StackPanel Orientation="Horizontal">
    <TextBlock Text="and">
    </TextBlock>
    <DatePicker x:Name="dateTo">
    </DatePicker>

Why am I not getting the right values? And how can I get the actual MIN and MAX dates to populate the Date.

Posted 1-Nov-14 19:07pm

B. Clay Shannon

Add a Solution

Comments

George Jonsson 2-Nov-14 1:59am

Try to use an object instead of DateTimeOffset. (The value you see is probably the default)
object dto = db.ExecuteScalar(sql);
Then set a breakpoint at this line and check what result you get.

B. Clay Shannon 2-Nov-14 19:27pm

I tried this:

internal static DateTimeOffset GetLatestDate()
{
object dto;
using (var db = new SQLite.SQLiteConnection(App.DBPath))
{
string sql = "SELECT MAX(dateTimeTaken) FROM PhotraxBaseData";
dto = db.ExecuteScalar<object>(sql);
}
return (DateTimeOffset)dto;
}

...but it fails with, "System.NotSupportedException was unhandled by user code
. . . Message=Don't know how to read System.Object"

This works without crashing:

internal static DateTimeOffset GetLatestDate()
{
object dto;
using (var db = new SQLite.SQLiteConnection(App.DBPath))
{
string sql = "SELECT MAX(dateTimeTaken) FROM PhotraxBaseData";
dto = db.ExecuteScalar<datetimeoffset>(sql);
}
return (DateTimeOffset)dto;
}

...but I still end up with "1/1/0001" as the value in dto.

All of the records in photraxbasedata contain date values, no nulls, no empty strings; the oldest date is 2008 and the newest 2014.

I then tried to make this code more like other working code, and changed it to this:

internal static DateTimeOffset GetLatestDate()
{
DateTime dto;
List<PhotraxBaseData> psets = new List<PhotraxBaseData>();
using (var db = new SQLite.SQLiteConnection(App.DBPath))
{
string sql = "SELECT MAX(dateTimeTaken) FROM PhotraxBaseData";
psets = db.Query<PhotraxBaseData>(sql);
}
dto = from a in psets select a.dateTimeTaken;
return dto;
}

...but get, "Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<system.datetime>' to 'System.DateTime'"

What? Why convert to itself. Or is there a difference between the pithy DateTime and the loquacius DateTime?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)