Very Computer

Please look up an article entitled "Using Date and Time
data" in BOL.

GETDATE() returns the current date AND time. Since you
don't seem to be entering times into your column, simply
use CONVERT to extract the date portion from GETDATE() as
follows:
WHERE table.datefield= CONVERT(char(8),GETDATE(),112)

An implicit conversion will convert the result of the
CONVERT function to a datetime which can be compared with
what's in your column.

HTH,
Bob Barrows

Quote:>-----Original Message-----
>How can I compare 2 dates? I have smaldatetime type of
field in a table.
>Although when I preview data in a table directly I see
1/1/1998, 12/1/2000
>and so on. Stored procedure returns something like 1998-
05-14 00:00:00.
>I need to select only records with particular date from
the table.
>If I do comparison like this:
>WHERE table.datefield=GETDATE()
>my stored procedure returns nothing. It's because hours,
minutes and seconds
>are different.
>I can use YEAR, MONTH and DAY functions with both dates,
but I think there
>is a simpler way to compare just dates.
>Thank you
>vasya

>.

Try,

where table.datefield = cast(convert(char(8), getdate(), 112) as datetime)

AMB

where dte = convert(varchar(8), getdate(), 112)

Quote:>-----Original Message-----
>How can I compare 2 dates? I have smaldatetime type of
field in a table.
>Although when I preview data in a table directly I see
1/1/1998, 12/1/2000
>and so on. Stored procedure returns something like 1998-
05-14 00:00:00.
>I need to select only records with particular date from
the table.
>If I do comparison like this:
>WHERE table.datefield=GETDATE()
>my stored procedure returns nothing. It's because hours,
minutes and seconds
>are different.
>I can use YEAR, MONTH and DAY functions with both dates,
but I think there
>is a simpler way to compare just dates.
>Thank you
>vasya

>.

Try something like this:

SELECT <Column List>
FROM <Table Name>
WHERE <Date Column> = CAST(CONVERT(char, GETDATE(), 112) AS datetime)

--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/

Quote:> How can I compare 2 dates? I have smaldatetime type of field in a table.
> Although when I preview data in a table directly I see 1/1/1998, 12/1/2000
> and so on. Stored procedure returns something like 1998-05-14 00:00:00.
> I need to select only records with particular date from the table.
> If I do comparison like this:
> WHERE table.datefield=GETDATE()
> my stored procedure returns nothing. It's because hours, minutes and
seconds
> are different.
> I can use YEAR, MONTH and DAY functions with both dates, but I think there
> is a simpler way to compare just dates.
> Thank you
> vasya

WHERE
    dateCOLUMN >= CONVERT(CHAR(8), GETDATE(), 112)
    AND
    dateCOLUMN < CONVERT(CHAR(8), GETDATE()+1, 112)

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.

Quote:> How can I compare 2 dates? I have smaldatetime type of field in a table.
> Although when I preview data in a table directly I see 1/1/1998, 12/1/2000
> and so on. Stored procedure returns something like 1998-05-14 00:00:00.
> I need to select only records with particular date from the table.
> If I do comparison like this:
> WHERE table.datefield=GETDATE()
> my stored procedure returns nothing. It's because hours, minutes and
seconds
> are different.
> I can use YEAR, MONTH and DAY functions with both dates, but I think there
> is a simpler way to compare just dates.
> Thank you
> vasya

Could you post the following result?

select top 5 yourColDate
from yourTable
order by yourColDate

AMB

What about,

select *
from yourTable
where yourColDate = '19980625'

or

select *
from yourTable

any output?

AMB

Quote:> When I exclude WHERE clause from my SP then I see records for let's say

Do you mean October 3, 2002? Or March 10, 2002?  Are you sure there are
records for today's date?

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.

I'm sorry gentlemen
I just was so stupid. I'm working with remote SQL Server and I did not realized that GETDATE is running there too and although I change the system date on my local machine to the date of records I see directly in a table, GETDATE returns today's date and there are no records for today.
All statements I was given are working. It was my fault.
Sorry and thanks to all of you
vasya

  What about,

  select *
  from yourTable
  where yourColDate = '19980625'

  or

  select *
  from yourTable

  any output?

  AMB

Quote:> > When I exclude WHERE clause from my SP then I see records for let's say
> 3/10/2002 date.

> Do you mean October 3, 2002? Or March 10, 2002?  Are you sure there are
> records for today's date?

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.

WHERE CAST(table.datefield AS int) = CAST(GETDATE() AS INT)

And this will probably execute a few milliseconds faster as well.

Mark

Mark,

this is not what you really want. also, this will negate the benefit of an
index on your datetime column.

if date is all you care then cast(getdate() as int) will not suffice.

select CAST(cast('20030113' as datetime) AS int),
    CAST(GETDATE() AS INT)

--
-oj
http://www.rac4sql.net

Quote:> Here's another way nobody has thought of yet.

> WHERE CAST(table.datefield AS int) = CAST(GETDATE() AS INT)

> And this will probably execute a few milliseconds faster as well.

> Mark

SK

SK