WHAT DO I USE TO MAKE A TODAY DATE QUERY?

[Fernand St-Georges]

select no_dossier from dbo.membre where date_MAJ = GETDATE()

select no_dossier from dbo.membre where date_MAJ = '2005-07-21'

Both should give me the same result, yes or no?



thanks

[Ronnie Chee]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

"Fernand St-Georges" <fernand.st-georges@videotr on.ca> wrote in message
news:QGQDe.4131 2$J63.365386@we ber.videotron.n et...[color=blue]
> select no_dossier from dbo.membre where date_MAJ = GETDATE()
>
> select no_dossier from dbo.membre where date_MAJ = '2005-07-21'
>
> Both should give me the same result, yes or no?
>
>
>
> thanks
>
>[/color]
No. The first GETDATE() returns a time component too.
Do select GETDATE(), '2005-07-21' to see the difference.

[Fernand St-Georges]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?



I know, but the problem is I cannot retreive the data I want as I use
select no_dossier from dbo.membre where date_MAJ = GETDATE()

it just does not return anything, but it does if I use the date like
this '2005-07-21'

what am I doing wrong? it works fine if I use date() in Access.

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

Fernand St-Georges (fernand.st-georges@videotr on.ca) writes:[color=blue]
> I know, but the problem is I cannot retreive the data I want as I use
> select no_dossier from dbo.membre where date_MAJ = GETDATE()
>
> it just does not return anything, but it does if I use the date like
> this '2005-07-21'
>
> what am I doing wrong? it works fine if I use date() in Access.[/color]

What you are doing wrong? Well, you know by now that getdate() will
give you a time component, and now you are asking why it does not
work?

Anyway, the best way to strip out the time component is:

convert(char(8) , getdate(), 112)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ross Presser]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

On Thu, 21 Jul 2005 18:07:49 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
> Anyway, the best way to strip out the time component is:
>
> convert(char(8) , getdate(), 112)[/color]

that leaves you with a char value. I prefer

convert(datetim e, floor(convert(f loat,getdate()) ))

even though it's a bit wordier, because it can't possibly be sensitive to
the region, and also because it has useful variants. Like this, which
rounds a time down to the start of the hour:

convert(datetim e, floor(convert(f loat,getdate()) * 24) / 24)

[Erland Sommarskog]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

Ross Presser (rpresser@NOSPA Mgmail.com.inva lid) writes:[color=blue]
> On Thu, 21 Jul 2005 18:07:49 +0000 (UTC), Erland Sommarskog wrote:[color=green]
>> Anyway, the best way to strip out the time component is:
>>
>> convert(char(8) , getdate(), 112)[/color]
>
> that leaves you with a char value. I prefer
>
> convert(datetim e, floor(convert(f loat,getdate()) ))
>
> even though it's a bit wordier, because it can't possibly be sensitive to
> the region,[/color]

Nope. Not format 112, which is YYYYMMDD. This format can only be
interpreted in one single way.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Fernand St-Georges]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

I got the value I needed using this type of query
WHERE (dbo.Membre.Dat e_MAJ > GETDATE() - 1) AND (dbo.Membre.Dat e_MAJ <
GETDATE() + 1)
I only thought this would do
where date_MAJ = GETDATE()

while in Access I only need
where date_MAJ =DATE()

kind of amazing isn't it


"Erland Sommarskog" <esquel@sommars kog.se> a écrit dans le message de news:
Xns969ACC7D071E 5Yazorman@127.0 .0.1...[color=blue]
> Fernand St-Georges (fernand.st-georges@videotr on.ca) writes:[color=green]
>> I know, but the problem is I cannot retreive the data I want as I use
>> select no_dossier from dbo.membre where date_MAJ = GETDATE()
>>
>> it just does not return anything, but it does if I use the date like
>> this '2005-07-21'
>>
>> what am I doing wrong? it works fine if I use date() in Access.[/color]
>
> What you are doing wrong? Well, you know by now that getdate() will
> give you a time component, and now you are asking why it does not
> work?
>
> Anyway, the best way to strip out the time component is:
>
> convert(char(8) , getdate(), 112)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Stu]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

Not amazing at all. Access has a DATE() function which only returns a
date. GETDATE() returns the more precise date/time. If you want to
compare apples to apples, in Access you would be running:

where date_MAJ =NOW()

I hate to sound like Celko, but do you understand the query you're
writing?

WHERE (dbo.Membre.Dat e_MAJ > GETDATE() - 1) AND
(dbo.Membre.Dat e_MAJ <
GETDATE() + 1)

is looking for dates that fall between NOW (in Access terms) and NOW
minus 1 day.

Stu

[Ross Presser]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

On 22 Jul 2005 06:15:53 -0700, Stu wrote:
[color=blue]
> WHERE (dbo.Membre.Dat e_MAJ > GETDATE() - 1) AND
> (dbo.Membre.Dat e_MAJ <
> GETDATE() + 1)
>
> is looking for dates that fall between NOW (in Access terms) and NOW
> minus 1 day.[/color]

actually, it's looking for dates that fall between NOW -1 and NOW + 1 day.
As I write this, therefore, it would match anything between 7/21 10:04 AM
EDT and 7/23 10:04 AM EDT.

[tw.ringo@gmail.com]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

Belive it or not, Microsoft made the sql syntax for Access different
than msSQL.

[Erland Sommarskog]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

Fernand St-Georges (fernand.st-georges@videotr on.ca) writes:[color=blue]
> I got the value I needed using this type of query
> WHERE (dbo.Membre.Dat e_MAJ > GETDATE() - 1) AND (dbo.Membre.Dat e_MAJ <
> GETDATE() + 1)
> I only thought this would do
> where date_MAJ = GETDATE()
>
> while in Access I only need
> where date_MAJ =DATE()
>
> kind of amazing isn't it[/color]

No, it's not all amazing. Access and SQL Server are two very different
products, and if you try to use SQL Server as if it was Access you
are in for a hard time.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Stu]

Re: WHAT DO I USE TO MAKE A TODAY DATE QUERY?

You're right. See how flabbergasted I was? :)