Reason - 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

General SQL Server Forums

New to SQL Server Programming

Reason

Author

Topic

mani_1234
Starting Member

24 Posts

Posted - 2012-10-04 : 06:41:22

i have table struct like this
create table #t
( a datetime )
a
--------------------------------------------
2012-10-04 15:33:27.560
2012-10-04 15:33:30.613
2012-10-04 15:33:32.800
2012-10-05 15:33:40.327
2012-10-04 15:39:57.433
2012-10-04 15:41:30.997

following are the queries ..
1.select * from #t
where CONVERT(varchar(10),a,120) ='2012-10-04'
2.select * from #t
where a like '2012-10-04%'
query 1 returns the correct ans. bt 2nd query s not returing ..
Any reason for output ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 06:49:30

The reason for what you are observing is that datetime is not stored as a string in the database. The internal representation for datetime is an 8-byte float that includes the date part and time part.

If you are looking for all the rows which have the date part = 2012-10-04, query it like this:

SELECT * FROM #t
WHERE a >= '20121004' AND a < '20121005';

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-04 : 07:05:14

I always liked this one:

SELECT dateadd(dd,datediff(dd,0,getdate()),0)

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 07:08:47

quote:
Originally posted by DonAtWork

I always liked this one:
SELECT dateadd(dd,datediff(dd,0,getdate()),0)

Is it October 4th already!?! I thought that was a random date that OP picked out of thin air!!

mani_1234
Starting Member

24 Posts

Posted - 2012-10-04 : 07:26:21

ohk thnx i got the point

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-04 : 08:54:44

quote:
Originally posted by sunitabeck
Is it October 4th already!?! I thought that was a random date that OP picked out of thin air!!

HA! i didn't realize it was October 4th either.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 03:08:00

Read this series of posts for better understanding http://beyondrelational.com/modules/2/blogs/70/posts/10897/understanding-datetime-column-part-i.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources