Re: Comparing dates in a query statement

Thanks for your help Sylvain.

I finally got it working. Long story short, I was comparing the text box
date against a date field I *thought* I had CONVERTed but hadn't.

Wes


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ebaV9QPqFHA.3544@xxxxxxxxxxxxxxxxxxxxxxx
> Yes, this should be sufficient to strip the time (or more exactly reset it
> to midnight). Personally I use nvarchar(10) instead of varchar but that
> shouldn't change anything.
>
> I don't know why this doesn't work with your unbound textbox. You should
> print the intermediary result to make sure that everything is OK. Apply
the
> Convert() function to the value of your textbox to see how it is
interpreted
> by SQL-Server.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Wes Peters" <me@xxxxxxxx> wrote in message
> news:11gpobs43pop72d@xxxxxxxxxxxxxxxxxxxxx
> > Thanks for the info.
> >
> > Does the following statement strip the time from a datetime field:
> >
> > CONVERT (datetime, CONVERT (varchar, tblOrders.DTStamp, 101), 101) AS
> > Placed
> >
> > When I compare the above field with the same date in an unbound textbox
> > using single quotes they don't show to be equal and I'm not sure why
> > unless
> > somehow the time part is still there.
> >
> > Any thoughts?
> >
> >
> > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> > wrote in message news:uvo0TPMqFHA.1032@xxxxxxxxxxxxxxxxxxxxxxx
> >> On SQL-Server, dates must be enclosed with single quote ' instead of
the
> >> pound sign #. With the correct option set, you can also use the double
> >> quote " but it's better to stick with the standard.
> >>
> >> You must use the international date format, yyyy/mm/dd or the US
format,
> >> mm/dd/yyyy or mm/dd/yy if the default language for the login is
english;
> >> otherwise you must use an appropriate format.
> >>
> >> The trouble with DateTime and SmallDateTime is that they have both a
time
> >> part that may be different from midnight; something that may cause a
lot
> > of
> >> problem when used with the equality operator = . The convert function
> >> can
> >> be used to strip away this time part; use a second call to the convert
> >> function to convert back to a DateTime or SmallDateTime field if you
> >> don't
> >> want some missy resultats with > and < .
> >>
> >> Finally, the best way is not to construct the query string but to use a
> >> query with parameters; as this will shield you from potential
> >> localization
> >> problems (and security/hacking problems, too).
> >>
> >> --
> >> Sylvain Lafontaine, ing.
> >> MVP - Technologies Virtual-PC
> >> E-mail: http://cerbermail.com/?QugbLEWINF
> >>
> >>
> >> "Wes Peters" <me@xxxxxxxx> wrote in message
> >> news:11gosh49u4n2jc8@xxxxxxxxxxxxxxxxxxxxx
> >> >I want to be able to compare a user-supplied date against a
> >> >smalldatetime
> >> > field in a query. It looks like using the # sign is a mdb thing
only.
> > Do
> >> > I
> >> > use the Convert function on the table's date field to convert to a
> >> > "formatted" smalldatetime and then compare? This is what I've tried
> > with
> >> > some success. It works when I use a > or < in the comparison but not
> >> > =.
> >> >
> >> > What is the best approach to constructing a query string that
compares
> >> > a
> >> > user-supplied date against smalldatetime field?
> >> >
> >> > Any thoughts or articles on the subject would be appreciated.
> >> >
> >> > Thanks,
> >> > Wes
> >> >
> >> >
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • still trying to compare dates
    ... causes the SQLserver not access an index defined on a datetime column. ... Documentation on DATEDIFF is flawed: ... Is the parameter that specifies on which part of the date to calculate ... Therefore comparing dates using ...
    (microsoft.public.sqlserver)
  • Re: Set selection to previous month
    ... because you're comparing a DateTime field to a number. ... Because these record selection formulas are wrong. ...
    (microsoft.public.vb.crystal)
  • Re: Comparing date and time
    ... You either have 2 DateTime values, or you have 2 strings in the format ... They are pure data. ... Note that I spoke of comparing DateTime values. ... Comparing 2 strings will only tell you which string is alphabetically before ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: date comparioson in a Case statement
    ... > I have a case statement where I am comparing two dates ... > Syntax error during implicit conversion of VARCHAR value 'NA' to a DATETIME ... I'm sure there is some ugly mess of cast statements you could make that ...
    (microsoft.public.sqlserver.programming)