Need help with SQL query with datetime object

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

Need help with SQL query with datetime object

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-12 : 19:05:40

Hi,
Can some one provide me query for following?

col1: Datetime
col2: Datetime

SELECT * from table
where (col1+30 >= curr_date AND
(col2 == null OR (curr_date >= col2+30 AND col1 > col2))
)

I'm new to SQL server. I'll appreicate if some one can provide me proper query for above psedue query.

Thanks in Advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 19:14:20

+30 is add 30 days ?


where dateadd(day, 30, col1) >= curr_date
and   ( col2 is null or (curr_date >= dateadd(day, 30, col2) and col1 > col2))
)

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

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-12 : 19:17:20

Hi khtan,
Thanks for the quick reply. I want to compare only dates in below comparison:
col1+30 >= curr_date
curr_date >= col2+30

And, with col1 > col2, I need to compare both date & timestamp. Will your solution work for this case? Also, is "day" a keyword in dateadd(day, 30, col1)?

Thanks again!

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-12 : 19:19:20

Also, I need current date function in sql server in place of curr_date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 19:30:54

quote:
Originally posted by freephoneid

Hi khtan,
Thanks for the quick reply. I want to compare only dates in below comparison:
col1+30 >= curr_date
curr_date >= col2+30

And, with col1 > col2, I need to compare both date & timestamp. Will your solution work for this case? Also, is "day" a keyword in dateadd(day, 30, col1)?

Thanks again!

change col1+30 >= curr_date
to

dateadd(day, 30, col1) >= dateadd(day, datediff(day, 0, getdate()), 0)

change curr_date >= col2+30 
to [code]
dateadd(day, datediff(day, 0, getdate()), 1) > dateadd(day, 30, col2)

Yes. day is a keyword

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

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-12 : 19:39:00

Thanks again for the reply. You meant

change curr_date >= col2+30
to
dateadd(day, datediff(day, 0, getdate()), 1) >= dateadd(day, 30, col2)

right? (= sign was missing in above)

Also, what about the time comparison for col1 > col2? I need both date n time comparison for col1>col2
For others, I need only date comparison. Will above query work?

Thanks!

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-12 : 20:09:58

Also, why do we need 1 instead of 0 in below datediff function & why comparison is not made with >=??

dateadd(day, datediff(day, 0, getdate()), 1) > dateadd(day, 30, col2)

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 20:56:03

quote:
Originally posted by freephoneid

Thanks again for the reply. You meant

change curr_date >= col2+30
to
dateadd(day, datediff(day, 0, getdate()), 1) >= dateadd(day, 30, col2)

right? (= sign was missing in above)

Also, what about the time comparison for col1 > col2? I need both date n time comparison for col1>col2
For others, I need only date comparison. Will above query work?

Thanks!

no. it is ">" .

dateadd(day, datediff(day, 0, getdate()), 0) will give you today's date at midnight 00:00:00

As the col2 contain date & time. Assuming today date is 2010-03-12, if col2 value after adding 30 days is 2010-03-12 12:30, the following condition will be false
dateadd(day, 30, col2) <= dateadd(day, datediff(day, 0, getdate()), 0)

as you are comparing '2010-03-12 12:30' <= '2010-03-12 00:00'

dateadd(day, datediff(day, 0, getdate()), 1) will add 1 day to todays date and give you '2010-03-13'.

So you need to compare using LESS THAN

dateadd(day, 30, col2) < dateadd(day, datediff(day, 0, getdate()), 1)

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 20:57:48

quote:
Originally posted by freephoneid

Also, why do we need 1 instead of 0 in below datediff function & why comparison is not made with >=??

dateadd(day, datediff(day, 0, getdate()), 1) > dateadd(day, 30, col2)

Thanks!

see my prev post.

Also run this and see the result


select	getdate() as now,
        dateadd(day, datediff(day, 0, getdate()), 0) AS today,
	dateadd(day, datediff(day, 0, getdate()), 1) AS tomorrow,
	dateadd(day, datediff(day, 0, getdate()), -1) AS yesterday

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

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-15 : 18:29:25

Great explaination. Thanks khtan!

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-15 : 19:15:46

Hi,
Looking at the final query below:

where dateadd(day, 30, col1) >= dateadd(day, datediff(day, 0, getdate()), 0)
and (col2 is null OR (dateadd(day, datediff(day, 0, getdate()), 1) > dateadd(day, 30, col2) and col1 > col2))

Is there any other way as well to compare only date part for col1 & col2 above, since this query seems little difficult to understand as compared to psedue code?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-15 : 19:29:00

if you are using SQL 2008, you can use the date data type,

do you need the time part in your data ? If not, you can consider removing it when inserting into the table

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

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-15 : 19:41:50

Hi,
I'm using SQL 2008 but I don't want to change the data type to "date", since there is historical data as well. I was just wondering if there is any other solution by working with datetime object but comparing only date in a simple way....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-15 : 19:47:49

there are other way like converting to string or even converting to date data type etc

convert(varchar(10), col1, 112)

convert(date, col1)

but it may not give you good performance. As you are applying a function on the column and with that, SQL Server will not be able to utilize any indexes you have on that column.

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

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-03-15 : 20:53:30

Hi khtan,
Thats what I needed to know which one is efficient. If the solution given by you is efficient, then I'm ok.....Thanks again!

This forum is excellent as compare to others as we get instant response!! Thanks for all the support!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources