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
 WHERE

Author  Topic 

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-14 : 08:45:18
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

30421 Posts

Posted - 2008-05-14 : 08:49:56
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 - 2008-05-14 : 10:11:01
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

3608 Posts

Posted - 2008-05-14 : 10:31:08
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

7423 Posts

Posted - 2008-05-14 : 11:55:11
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
Go to Top of Page
   

- Advertisement -