getdate() default value 1

[MikeT]

I have a date/time field named EntryDate with a default value of getdate() so when a record is created the current date and time are recorded. The timestamp looks like this: 6/8/2005 10:47:49 AM

if I issue these

select * from mytable where entrydate='6/8/2005 10:47:49 AM'
select * from mytable where entrydate='6/8/2005'
select * from mytable where entrydate like '6/8/2005%%'

I get 0 results. However, this would give me results:

select * from mytable where entrydate BETWEEN '6/7/05' AND '6/9/05'

How can I access these records using "entrydate=..." instead of "entrydate BETWEEN..."?

 

[jbenson001]

select <cols> from <table>
where
convert(char(10), <date col>, 101) = '06/08/2005'

 

[SajidAttar]

you will get more info on date here

FAQ183-5834.

 

[JamesLean]

When searching datetime data you are better off NOT using a function over the column as this will stop any index being used. Instead try something like this (to find all entries on June 8th):

WHERE entrydate >= '20050608'
  AND entrydate < '20050609'

--James

 

[SQLBill]

6/8/2005

Is that June 8th or 6th of August???

How is SQL Server supposed to know which it is. When you use dates that are not YYYYMMDD, add SET DATEFORMAT to your script.

SET DATEFORMAT DMY
SET DATEFORMAT MDY
SET DATEFORMAT YMD

Just use whichever one of the above applies to how your dates are being provided.

-SQLBill

Posting advice: FAQ481-4875