datetime selection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

datetime selection

Author

Topic

lazydev
Starting Member

16 Posts

Posted - 2008-04-14 : 08:41:47

This is my sql query

Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%'

there are 8 records on this time.But it displays no result .
The column is datetime datatype
year month date or we need to mention any thing other than this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 08:44:18

Select DOJ AS JoiningDate from emp
where DOJ>='20080904' and DOJ<'20080905'

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 08:44:48

LIKE operator works on character data not on Datetime.

Select DOJ AS 'JoiningDate' from emp where dateadd(day, datediff(day, 0, DOJ), 0) = '20080904'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 08:47:18

Also dont use single quotes around alias name

select data from
(
select 1 as data
) as t

select 'data' from
(
select 1 as 'data'
) as t

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 08:49:03

quote:
Originally posted by harsh_athalye

LIKE operator works on character data not on Datetime.

Select DOJ AS 'JoiningDate' from emp where DOJ = '20080904'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

This wont return all datetime values from table for the given day. It looks only for exact values and wont match all those records which has a time part attached to it for 04/09/2008.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 08:57:38

quote:
Originally posted by visakh16

quote:
Originally posted by harsh_athalye

LIKE operator works on character data not on Datetime.

Select DOJ AS 'JoiningDate' from emp where DOJ = '20080904'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

This wont return all datetime values from table for the given day. It looks only for exact values and wont match all those records which has a time part attached to it for 04/09/2008.

Yes I realised that after posting. Madhi's solution is much more better.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources