How to retrieve record whose date is=to today date

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

How to retrieve record whose date is=to today date

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-06-05 : 06:49:28

Hai All,

This is my table structure

Email (Tablename)

EmailId int
from_email varchar(200)
to_email varchar(200)
subject varchar(350)
description varchar(1024)
date_to_send datetime

Now, I want to retrieve records, whose date field should equal to todays date

Suresh Kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 06:53:55

[code]
select *
from Email
where date_to_send >= dateadd(day, datediff(day, 0, getdate()), 0)
and date_to_send < dateadd(day, datediff(day, 0, getdate()), 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 06:55:03

select * from Email
where date_to_send >= '20090605'
and date_to_send < '20090606'

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-05 : 07:00:13

select * from Email
where date_to_send=getdate()

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 07:26:09

quote:
Originally posted by senthil_nagore

select * from Email
where date_to_send=getdate()

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

= getdate() ? ? you might not get anything back

KH
[spoiler]Time is always against us[/spoiler]

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-06-05 : 08:08:10

Thanks Khtan
your query is working.

Hi Senthil

select * from Email
where date_to_send=getdate()

if we execute your query, we might not get any records, the problem is there will be minute diffrence between date_to_send and getdate() in seconds...

Suresh Kumar

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-05 : 08:32:29

quote:
Originally posted by soori457

Thanks Khtan
your query is working.

Hi Senthil

select * from Email
where date_to_send=getdate()

if we execute your query, we might not get any records, the problem is there will be minute diffrence between date_to_send and getdate() in seconds...

Then convert the getdate() according to ur column

where date_to_send=convert(varchar,getdate(),101)

Suresh Kumar

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-05 : 08:32:53

Then convert the getdate() according to ur column

where date_to_send=convert(varchar,getdate(),101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 08:36:41

quote:
Originally posted by senthil_nagore

Then convert the getdate() according to ur column

where date_to_send=convert(varchar,getdate(),101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

1. If you are converting the date to string, should convert to ISO format YYYYMMDD as it is not dependent on the date format of your system.

2. If your date_to_send is a datatime column and does not contain time, then you can compare with a string. Else you will need to use >= and < as what i did.

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-05 : 08:40:01

Hi khtan,

Thanks for your information!

now its ok ?

where convert(varchar,date_to_send,101)=convert(varchar,getdate(),101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 08:47:04

quote:
Originally posted by senthil_nagore

Hi khtan,

Thanks for your information!

now its ok ?

where convert(varchar,date_to_send,101)=convert(varchar,getdate(),101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

It will work. But the performance might not be good. Cause when you applying a function to a column, SQL server will not be able to utilize any indexes on that column .

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-05 : 08:50:27

quote:
Originally posted by khtan

quote:
Originally posted by senthil_nagore

Hi khtan,

Thanks for your information!

now its ok ?

where convert(varchar,date_to_send,101)=convert(varchar,getdate(),101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

It will work. But the performance might not be good. Cause when you applying a function to a column, SQL server will not be able to utilize any indexes on that column .

KH
[spoiler]Time is always against us[/spoiler]

Thanks khtan,

Very valuable information, those information are not available in any book. It can be developed by exp only. Thanks for sharing it.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 08:56:37

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources