DateTime short formatted range lookup

[Matt Brown - identify]

Hello,

I'm trying to figure out a method to look up by a range of dates,
entries in a database. The format of the date in the database is "M\D
\yyyy HH:MM:SS".

What i need to do is take the dates that are in the database
(formatted as above), isolate the date (which is in short format) and
compare it to the short formatted date in a datetimepicker.

How can I take a string and format it as a short date?

How can I compare two short dates, to find if the date falls in the
date range? (isolate each part of the date? is there an actual
function that handles this?)


Any help is greatly appreciated,

Matt

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Hello,

I'm trying to figure out a method to look up by a range of dates,
entries in a database. The format of the date in the database is "M\D
\yyyy HH:MM:SS".

Why are you storing the date as a string in the database? Store it as a
date.

What i need to do is take the dates that are in the database
(formatted as above), isolate the date (which is in short format) and
compare it to the short formatted date in a datetimepicker.

How can I take a string and format it as a short date?

You can't. You first have to convert it to a date, then you can format
it to a string.

How can I compare two short dates, to find if the date falls in the
date range?

You can't. The date format that you are using is not comparable.

(isolate each part of the date? is there an actual
function that handles this?)

The DateTime structure has a Date property that gives you the date part,
i.e. a DateTime value where the time part is 00:00:00.

However, you don't need any of that for what you are doing. Get the date
from the DateTimePicker, and calculate the starting and ending time of
the interval. Then you can easily select the records from the database
where the date is between them.

That is of course if you change the database so that you store the date
as a date, so that it's possible to compare it to anything at all.

 

[Matt Brown - identify]

Why are you storing the date as a string in the database? Store it as a
date.

It is stored as datetime.

You can't. You first have to convert it to a date, then you can format
it to a string.

I don't need to convert it.

You can't. The date format that you are using is not comparable.

Are they both short dates?
1) M/D/yyyy
2) M/D/yyyy HH:MM:SS

If not, there must be a conversion?

The DateTime structure has a Date property that gives you the date part,
i.e. a DateTime value where the time part is 00:00:00.

However, you don't need any of that for what you are doing. Get the date
from the DateTimePicker, and calculate the starting and ending time of
the interval. Then you can easily select the records from the database
where the date is between them.

That is of course if you change the database so that you store the date
as a date, so that it's possible to compare it to anything at all.

Can you give me some syntax or examples? for instance, if the date
time pickers are called dateStart and dateEnd, how would i compare the
two?

I'm guessing that the variable that I'm setting the date value out of
the datebase to, needs to be a datetime?

I'm new at this.


Thanks!

Matt

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

It is stored as datetime.

You said that the date had a specific format, that's why I assumed that
it was a string. A datetime value doesn't have a format.

I don't need to convert it.


Are they both short dates?
1) M/D/yyyy
2) M/D/yyyy HH:MM:SS

These are date format, not dates. DateTime values doesn't have a format,
they only get a format when you convert them to strings. A DateTime
value always contains a date and a time component.

If not, there must be a conversion?

If you convert the dates into strings in that format, you can't compare
them.

Can you give me some syntax or examples? for instance, if the date
time pickers are called dateStart and dateEnd, how would i compare the
two?

You do like this in the SQL query:

.... where DateFieldInDataBase >= @Start and DateFieldInDateBase < @End

@Start and @End are parameters. You add Parameter objects to the Command
object with the values from dateStart and dateEnd.

If dateEnd is the date of the last day, you use dateEnd.AddDays(1) to
get the end time.

I'm guessing that the variable that I'm setting the date value out of
the datebase to, needs to be a datetime?

Yes.