By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Sep 30 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

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' )

Sep 30 '05 #2

P: n/a
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)
Sep 30 '05 #3

P: n/a
>> created_date LIKE '2005-09-29%' <<

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

Oct 1 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.