todays date

[esthera]

can you help me?
I want to query as follows:

select top 1 * from tableb where date=now()

I think my sytax is wrong but date is a datetime field and I want to select records that match todays date -- how do I do this?

also what syntax should i use when i insert into tableb(date)values (datefield)

please advise -- i only want to look at date and now time.

[guelphdad]

if you are using TOP then you aren't using MySQL. I'll move the thread to the general database area.

[nikkiH]

Originally Posted by esthera

can you help me?
I want to query as follows:

select top 1 * from tableb where date=now()

I think my sytax is wrong but date is a datetime field and I want to select records that match todays date -- how do I do this?

Using SQL Server?
You don't want an = because of the milliseconds. And I think "date" is a reserved word, so if that's your field name, use brackets. You want something like this.

This covers the last 24 hours.

select top 1 * from tableb where [date] > getdate()-1 and [date] <= getdate()

This covers "today" but note that converting slows down queries pretty bad, depending on indexes (I work with huge tables; this may not be an issue for you). The bigger the table, the worse this is.

where [date] >= convert(varchar,getdate(),101)

Originally Posted by esthera

also what syntax should i use when i insert into tableb(date)values (datefield)

did you have a problem with that?
Use brackets and try again.
insert into tableb([date]) values (getdate())

You may have a problem using getdate() in a user defined function or stored procedure.