Date formats

I am currently supporting an application that runs in SQL
Server 2000. One of our customers is having a problem
with filtering items from the database using dates. The
problem is that they can only filter on dates using US
date format, ie mm/dd/yy, yet once the day exceeds 12 they
can no longer view any data from the database. So if they
filter on 09/12/03 that will work fine and pull back data
from the 12th of September. If they filter on 09/13/03 it
does not pull anything back, even though there is data
from that day. Using UK format dates does not pull any
data back at all. This does not happen for any of my
other customers who are running the same application in
SQL Server 2000. Is there an option that can be set
either on the database or on the server that can affect
this?
Any ideas would be greatly appreciated.Ideally this requires a code change in your application.
Your app needs to pass dates around in a non ambiguous
format eg :- '13 Mar 2003', this format can cause problems
if you are running multiple languages though. The safeest
bet is yyyymmdd SQL will always interpret this as ymd.
If a code change isn't possible and you want a quick fix
for this client, compare the regional settings in control
panel to the troublesome machine to a known good one. I
imagine it's running English (US) and everyone else is
using English (UK) or vice versa.
HTH
Ryan
>--Original Message--
>I am currently supporting an application that runs in SQL
>Server 2000. One of our customers is having a problem
>with filtering items from the database using dates. The
>problem is that they can only filter on dates using US
>date format, ie mm/dd/yy, yet once the day exceeds 12
they
>can no longer view any data from the database. So if
they
>filter on 09/12/03 that will work fine and pull back data
>from the 12th of September. If they filter on 09/13/03
it
>does not pull anything back, even though there is data
>from that day. Using UK format dates does not pull any
>data back at all. This does not happen for any of my
>other customers who are running the same application in
>SQL Server 2000. Is there an option that can be set
>either on the database or on the server that can affect
>this?
>Any ideas would be greatly appreciated.
>.
>

Date Filter in Report Builder

The "On or Before" filter does not work with the 'On' part of the
filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
but does show 29/11/2006.
Is this a bug or maybe i'm missing something.
Regards
TobiI haven't used Report Builder that much, but its possible that Report
Builder is using the format of 30/11/2006 12:00:00 (12:00 am), and your
data from the db is 30/11/2006 09:15:00 (9:15 am).
Tobi wrote:
> The "On or Before" filter does not work with the 'On' part of the
> filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
> but does show 29/11/2006.
> Is this a bug or maybe i'm missing something.
>
> Regards
> Tobi|||I agree with Topher's assessment. If this was an actual report, I would
suggest creating an internal report parameter that adds one day and
then subtracts one second from the original parameter value. You would
then change your query parameter to use the new, internal parameter
value.
However, this is Report Builder, which I have not used much. Try to do
something like I just described.
Just had another thought... In your model, try to strip the times out.
If you aren't using them anyways, then you don't need them. If
everything has a time of 00:00:00 (12:00:00 AM), then your formula
should work just fine.
HTH,
Josh
Topher wrote:
> I haven't used Report Builder that much, but its possible that Report
> Builder is using the format of 30/11/2006 12:00:00 (12:00 am), and your
> data from the db is 30/11/2006 09:15:00 (9:15 am).
>
> Tobi wrote:
> > The "On or Before" filter does not work with the 'On' part of the
> > filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
> > but does show 29/11/2006.
> >
> > Is this a bug or maybe i'm missing something.
> >
> >
> > Regards
> >
> > Tobi

Date as part of primary key?

I have three items which make up the primary key:
ProjectNumber, Hardware, Date
Problem: The Date is formatted with date and time, how can I add a record and use just the date?
Currently I use GetDate() in my stored proc.
Mike BDates are not stored in any particular format. They are stored as a numeric value that indicates the number of days elapsed from a baseline date and the number of seconds elapsed since midnight (the decimal portion).

If you only want to store the whole-date portion, then instead of storing getdate store the results of this expression:

convert(char(10), getdate(), 120)

This will yield a time value equal to midnight. It's up to your interface to determine how the value is displayed.|||Dates are not stored in any particular format. They are stored as a numeric value that indicates the number of days elapsed from a baseline date and the number of seconds elapsed since midnight (the decimal portion).

If you only want to store the whole-date portion, then instead of storing getdate store the results of this expression:

convert(char(10), getdate(), 120)

This will yield a time value equal to midnight. It's up to your interface to determine how the value is displayed.
Exactly what I wanted, thank you!

Mike B