Can't "SELECT" using CREATED_DATE column...help

[trint]

Ok,
What format is this to where I can't find records (that I know are
there) using this date "2005-09-29 23:58:00".
Here is the SELECT that I use that doesn't work:

SELECT * FROM RMA_HEADER
WHERE CREATED_DATE LIKE '2005-09-29%'

Any help is appreciated.
Thanks,
Trint

[Zero.NULL]

create table MY_RMA_HEADER (CREATED_DATE datetime)

insert into MY_RMA_HEADER values(getdate())
insert into MY_RMA_HEADER values(dateadd(hh, -1, getdate()))
insert into MY_RMA_HEADER values(dateadd(hh, -2, getdate()))
insert into MY_RMA_HEADER values(dateadd(hh, -3, getdate()))
insert into MY_RMA_HEADER values(dateadd(hh, 1, getdate()))
insert into MY_RMA_HEADER values(dateadd(dd, 1, getdate()))
-- it will fetch data for 30th Sept. 2005
-- I have used 103 for formatting, u can use other as well, the idea is
to truncate time part
SELECT convert(datetime, convert(varchar, CREATED_DATE, 103)) FROM
MY_RMA_HEADER
WHERE convert(datetime, convert(varchar, CREATED_DATE, 103)) =
convert(datetime, '20050930' )

[Hugo Kornelis]

On 30 Sep 2005 04:12:28 -0700, trint wrote:

Ok,
What format is this to where I can't find records (that I know are
there) using this date "2005-09-29 23:58:00".
Here is the SELECT that I use that doesn't work:

SELECT * FROM RMA_HEADER
WHERE CREATED_DATE LIKE '2005-09-29%'

Any help is appreciated.
Thanks,
Trint

Hi Trint,

If the Created_Date is of datetime format, then use

SELECT Col1, Col2, ... -- Don't use SELECT * !!!
FROM RMA_Header
WHERE Created_Date >= '20050929'
AND Created_Date < '20050930'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

>> created_date LIKE '2005-09-29%' <<

Temporal data types are not strings, so you cannot use string functions
on them.