|
Home > Archive > Microsoft SQL Server forum > July 2005 > WHAT DO I USE TO MAKE A TODAY DATE QUERY?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
WHAT DO I USE TO MAKE A TODAY DATE QUERY?
|
|
| Fernand St-Georges 2005-07-21, 1:23 pm |
| 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 2005-07-21, 1:23 pm |
| "Fernand St-Georges" <fernand.st-georges@videotron.ca> wrote in message
news:QGQDe.41312$J63.365386@weber.videotron.net...
> 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
>
>
No. The first GETDATE() returns a time component too.
Do select GETDATE(), '2005-07-21' to see the difference.
| |
| Fernand St-Georges 2005-07-21, 1:23 pm |
|
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.droptable.com ***
| |
| Erland Sommarskog 2005-07-21, 1:23 pm |
| Fernand St-Georges (fernand.st-georges@videotron.ca) writes:
> 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.
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@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| Ross Presser 2005-07-21, 8:24 pm |
| On Thu, 21 Jul 2005 18:07:49 +0000 (UTC), Erland Sommarskog wrote:
> Anyway, the best way to strip out the time component is:
>
> convert(char(8), getdate(), 112)
that leaves you with a char value. I prefer
convert(datetime, floor(convert(float,
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(datetime, floor(convert(float,
getdate()) * 24) / 24)
| |
| Erland Sommarskog 2005-07-21, 8:24 pm |
| Ross Presser (rpresser@NOSPAMgmai
l.com.invalid) writes:
> On Thu, 21 Jul 2005 18:07:49 +0000 (UTC), Erland Sommarskog wrote:
>
> that leaves you with a char value. I prefer
>
> convert(datetime, floor(convert(float,
getdate())))
>
> even though it's a bit wordier, because it can't possibly be sensitive to
> the region,
Nope. Not format 112, which is YYYYMMDD. This format can only be
interpreted in one single way.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| Fernand St-Georges 2005-07-22, 7:23 am |
| I got the value I needed using this type of query
WHERE (dbo.Membre.Date_MAJ > GETDATE() - 1) AND (dbo.Membre.Date_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@sommarskog.se> a écrit dans le message de news:
Xns969ACC7D071E5Yazo
rman@127.0.0.1...
> Fernand St-Georges (fernand.st-georges@videotron.ca) writes:
>
> 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@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| 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.Date_MAJ > GETDATE() - 1) AND
(dbo.Membre.Date_MAJ <
GETDATE() + 1)
is looking for dates that fall between NOW (in Access terms) and NOW
minus 1 day.
Stu
| |
| Ross Presser 2005-07-22, 9:23 am |
| On 22 Jul 2005 06:15:53 -0700, Stu wrote:
> WHERE (dbo.Membre.Date_MAJ > GETDATE() - 1) AND
> (dbo.Membre.Date_MAJ <
> GETDATE() + 1)
>
> is looking for dates that fall between NOW (in Access terms) and NOW
> minus 1 day.
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 2005-07-22, 9:23 am |
| Belive it or not, Microsoft made the sql syntax for Access different
than msSQL.
| |
| Erland Sommarskog 2005-07-22, 11:23 am |
| Fernand St-Georges (fernand.st-georges@videotron.ca) writes:
> I got the value I needed using this type of query
> WHERE (dbo.Membre.Date_MAJ > GETDATE() - 1) AND (dbo.Membre.Date_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
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@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| You're right. See how flabbergasted I was? :)
|
|
|
|
|