andash
Posts: 18
|
| Posted: 04/12/2004, 3:32 PM |
|
Hi, I have a date field in my database stored as DateTime etc... I display it in a different format depending on use.
Being sensible  in New Zealand we show dates as DD/MM/YYYY. I am having problems searching on the date, Such as show all record on this date.
What I cant work out (because I get different results on different pages depending on how I have formated the dates displayed in the grid) is on what the search is done against. Is it performed against the records in the database in the format that they are in naturally or is it in the format in which I have displayed them? or a combination?
_________________
Andrew Asher
www.it3.co.nz |
 |
 |
peterr
Posts: 5971
|
| Posted: 04/12/2004, 8:30 PM |
|
May I ask which database are you using?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
andash
Posts: 18
|
| Posted: 04/12/2004, 8:59 PM |
|
MS SQL2000 and IIS & ASP CCS ver 2.2.3.60
_________________
Andrew Asher
www.it3.co.nz |
|
|
|
peterr
Posts: 5971
|
| Posted: 04/12/2004, 9:41 PM |
|
OK. I think that all you need is to specify the proper database format, either in the server connection settings or in the "DBFormat" property of the control, or in the Parameter configuration of the "Data Source" property.
The "Date Format" specified in the Connection (server) will be always used as the default if the "DBFormat" property of the control is blank, or if the Parameter's "Format" is blank.
The display format is independent from the database format and should have no effect on your search/query.
I would especially recommend that you specify the correct date format in the left section of the Data Source -> Parameter window
Please also refer to http://forums.codecharge.com/posts.php?post_id=44518
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
DonB
|
| Posted: 04/13/2004, 1:27 PM |
|
You must be mindful of the fact SQLServer will imply the time as 00:00:00 if
you specify only the dd/mm/yyyy so, to get all records for a given date you
will need to construct the WHERE to be thedate >= somedate + "00:00:00" AND
thedate <= somedate + "12:59:59" (ignoring that last second of the day .
Another option is the BETWEEN operator. But the important factor is that
SQLserver does not like to operate on mere "date" values, always with the
date and time combined. You can CONVERT the database column datetime to a
string and truncate it - then have a WHERE that looks like
value="dd/mm/yyyy", but this is not a good technique as the queries will not
be indexable.
Note that the above example is pseudo-SQL, you'd want to utilize SQLserver
date functions to calculate the bounds or CONVERT somedate to a string and
concatenate. (A sidenote - use the apostrophe to delimit literal strings in
your queries, not quotation marks)
--
DonB
http://www.gotodon.com/ccbth
"andash" <andash@forum.codecharge> wrote in message
news:6407b65bfe8dec@news.codecharge.com...
> MS SQL2000 and IIS & ASP CCS ver 2.2.3.60
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
