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.
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.dataWHERE (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" |
|
|
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')) |
|
|
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. |
|
|
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 datetimedeclare @end datetimeset @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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|