SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 WHERE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 05/14/2008 :  08:45:18  Show Profile  Reply with Quote
I would like to filter SesDate column resulting to May 10. Is there a problem with this formula?


SELECT data.*
FROM dbo.data
WHERE (SesDate = CONVERT(datetime, CONVERT(nvarchar, DATEPART(mm, CONVERT(datetime, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())), GETDATE()),
101))) + '/' + CONVERT(nvarchar, DATEPART(dd, CONVERT(datetime, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())), GETDATE()), 101)))
+ '/' + CONVERT(nvarchar, DATEPART(yy, CONVERT(datetime, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())), GETDATE()), 101)))))

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/14/2008 :  08:49:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh man, your dates again...

It works, but why don't you learn what we have posted so many times before?
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '18991230')


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 05/14/2008 :  10:11:01  Show Profile  Reply with Quote
Yes I'm getting it little by little. How can you make the week move every other Sundays? In this formula it moves Mondays.

WHERE (ReviewDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '18991230') - 13) AND (ReviewDate <= DATEADD(DAY, DATEDIFF(DAY,
'19000101', GETDATE()) / 7 * 7, '18991230'))
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 05/14/2008 :  10:31:08  Show Profile  Reply with Quote
Perhaps you should give up and call in a SQL expert or go on a training course, you never know, you might learn something that way.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/14/2008 :  11:55:11  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
cutiebo2t -- do not put complicated date expressions in a WHERE clause like that! Practice and test things by settings and /or printing VARIABLES, so you can review that your formulas are actually working.

i.e., do not write:


select ... from .... 
where date between <complicated expression 1> and <complicated expression 2>

instead, write:

declare @start datetime
declare @end datetime

set @start = <complicated expression 1>
set @end = <complicated expression 2>

-- print out @start and @end to make sure your expressions are working:

select @start as Start, @end as End

-- Once @start and @end are working perfectly, uncomment out the following line to run your query:

-- select ... from ... where date between @start and @end


This is how you learn about date expressions, and how they work, and how to practice and test and review things. It is impossible to know for sure if your date math is working if you have to execute a SELECT and examine data to see if things look OK; remove the guesswork and focus on calculating, examining, and testing a STARTDATE and an ENDDATE for your situation.

Does this make sense? This is an important thing to understand if you want to have any success as a programmer, in any language. You can't just stuff random pieces of code into your apps and keep running it over and over hoping for success. You need to isolate and carefully test small things to make sure that you completely, 100% understand what is happening and how it is working, and THEN you incorporate that into your larger code.





- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 05/14/2008 11:58:54
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000