October 8, 2013 at 11:43 am
Does anyone know what is wrong with this query? I'm trying to make a date range 7 days before 'today' and 21 days after 'today':
"SELECT tbl_pac2k_remarks.[Change Request] as ChangeRequest, tbl_pac2k_remarks.[Vetting Status] as VettingStatus, tbl_pac2k_remarks.[Clarification Remark] as ClarificationRemark, tbl_pac2k.[CHANGE REQUEST], tbl_pac2k.STATUS, tbl_pac2k.[Installation Start Date], tbl_pac2k.[Installation End Date], tbl_pac2k.[REQUESTER NAME], tbl_pac2k.DESCRIPTION, tbl_pac2k.[OUTAGE REQUIRED], tbl_pac2k.[BUSINESS IMPACT], tbl_pac2k.[Approval Type], tbl_pac2k.[Approval Group], tbl_pac2k.[OWNER GROUP], tbl_pac2k.[OWNER TECHNOLOGY GROUP], tbl_pac2k.CRL FROM tbl_pac2k_remarks INNER JOIN tbl_pac2k ON tbl_pac2k_remarks.[Change Request] = tbl_pac2k.[CHANGE REQUEST] WHERE (tbl_pac2k.[Installation Start Date] > DATE_SUB([Installation Start Date],INTERVAL 7 DAY)) AND WHERE (tbl_pac2k.[Installation Start Date] < DATE_ADD ([Installation Start Date],INTERVAL 21 DAY)) ORDER BY tbl_pac2k.[OWNER GROUP], tbl_pac2k.[Installation Start Date]"
October 8, 2013 at 11:56 am
are you MYSQL by any chance.?..this is Microsoft SQL forum
maybe someone else will chime in....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 8, 2013 at 11:57 am
Aside from the obvious MySql you have the word WHERE twice. You can't have two where clauses in a single query. You can have multiple condition. Remove the second WHERE and it should be closer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2013 at 12:05 pm
Actually this is a a SQL Server query embedded in a datasource on an asp.net page... not MySQL.... I removed the second WHERE and an error showed up:
Incorrect syntax near '7'.
October 8, 2013 at 12:32 pm
briancampbellmcad (10/8/2013)
Actually this is a a SQL Server query embedded in a datasource on an asp.net page... not MySQL.... I removed the second WHERE and an error showed up:Incorrect syntax near '7'.
Well, DATE_SUB and DATE_ADD are MySQL functions, so....
For T-SQL, you want DATEADD...
SELECT
tbl_pac2k_remarks.[Change Request] as ChangeRequest,
tbl_pac2k_remarks.[Vetting Status] as VettingStatus,
tbl_pac2k_remarks.[Clarification Remark] as ClarificationRemark,
tbl_pac2k.[CHANGE REQUEST],
tbl_pac2k.STATUS,
tbl_pac2k.[Installation Start Date],
tbl_pac2k.[Installation End Date],
tbl_pac2k.[REQUESTER NAME],
tbl_pac2k.DESCRIPTION,
tbl_pac2k.[OUTAGE REQUIRED],
tbl_pac2k.[BUSINESS IMPACT],
tbl_pac2k.[Approval Type],
tbl_pac2k.[Approval Group],
tbl_pac2k.[OWNER GROUP],
tbl_pac2k.[OWNER TECHNOLOGY GROUP],
tbl_pac2k.CRL
FROM tbl_pac2k_remarks
INNER JOIN tbl_pac2k
ON tbl_pac2k_remarks.[Change Request] = tbl_pac2k.[CHANGE REQUEST]
WHERE (tbl_pac2k.[Installation Start Date] > DATEADD(DAY, -7, [Installation Start Date]))
AND (tbl_pac2k.[Installation Start Date] < DATEADD (DAY, 21, [Installation Start Date]))
ORDER BY
tbl_pac2k.[OWNER GROUP],
tbl_pac2k.[Installation Start Date]
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 8, 2013 at 1:08 pm
I put in WHERE (tbl_pac2k.[Installation Start Date] > DATEADD(DAY, -7, [Installation Start Date])) AND (tbl_pac2k.[Installation Start Date] < DATEADD (DAY, 21, [Installation Start Date]))
into my embeddded query and it did not filter as would be expected.... I tried different puctuation of "(" and ")" and still it failed.
October 8, 2013 at 1:22 pm
briancampbellmcad (10/8/2013)
I put in WHERE (tbl_pac2k.[Installation Start Date] > DATEADD(DAY, -7, [Installation Start Date])) AND (tbl_pac2k.[Installation Start Date] < DATEADD (DAY, 21, [Installation Start Date]))into my embeddded query and it did not filter as would be expected.... I tried different puctuation of "(" and ")" and still it failed.
I think you have some logic issues going on here.
You are trying to find all rows where the value in the column [Installation Start Date] is the same [Installation Start Date] - 7. That doesn't work.
The value of that column does not change within a single row. What are you actually trying to do here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2013 at 1:30 pm
Sean Lange (10/8/2013)
briancampbellmcad (10/8/2013)
I put in WHERE (tbl_pac2k.[Installation Start Date] > DATEADD(DAY, -7, [Installation Start Date])) AND (tbl_pac2k.[Installation Start Date] < DATEADD (DAY, 21, [Installation Start Date]))into my embeddded query and it did not filter as would be expected.... I tried different puctuation of "(" and ")" and still it failed.
I think you have some logic issues going on here.
You are trying to find all rows where the value in the column [Installation Start Date] is the same [Installation Start Date] - 7. That doesn't work.
The value of that column does not change within a single row. What are you actually trying to do here?
agree......
USE [tempdb]
CREATE TABLE [dbo].[tbl_pac2k](
[Installation Start Date] [datetime] NULL
)
INSERT INTO [tbl_pac2k]([Installation Start Date])
VALUES(CAST('20131001 00:00:00.000' as DATETIME))
INSERT INTO [tbl_pac2k]([Installation Start Date])
VALUES(CAST('20131020 00:00:00.000' as DATETIME))
INSERT INTO [tbl_pac2k]([Installation Start Date])
VALUES(CAST('20130914 00:00:00.000' as DATETIME))
INSERT INTO [tbl_pac2k]([Installation Start Date])
VALUES(CAST('20130501 00:00:00.000' as DATETIME))
select * from tbl_pac2k
select * from tbl_pac2k
WHERE (tbl_pac2k.[Installation Start Date] > DATEADD(DAY, -7, [Installation Start Date]))
AND (tbl_pac2k.[Installation Start Date] < DATEADD (DAY, 21, [Installation Start Date]))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 8, 2013 at 1:30 pm
I'm trying to find all the records that are 1 week before today and 3 weeks after today including today.
October 8, 2013 at 2:01 pm
briancampbellmcad (10/8/2013)
I'm trying to find all the records that are 1 week before today and 3 weeks after today including today.
try here
http://www.sqlservercentral.com/Forums/Topic1418640-392-1.aspx
🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 8, 2013 at 2:23 pm
I am not a huge proponent of always making sure everything is ANSI compliant and "cross language compatible". If you are changing databases it is massive undertaking.
In sql server I would do that like this.
select * from tbl_pac2k
WHERE (tbl_pac2k.[Installation Start Date] > DATEADD(DAY, -7, getdate()))
AND (tbl_pac2k.[Installation Start Date] < DATEADD (DAY, 21, getdate()))
You could change getdate() to CURRENT_TIMESTAMP and it will work in most other dbms.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2013 at 2:35 pm
That worked... humble thanks! -
WHERE (tbl_pac2k.[Installation Start Date] > GETDATE() -8) AND (tbl_pac2k.[Installation Start Date] < GETDATE() +22)
October 8, 2013 at 2:40 pm
briancampbellmcad (10/8/2013)
That worked... humble thanks! -WHERE (tbl_pac2k.[Installation Start Date] > GETDATE() -8) AND (tbl_pac2k.[Installation Start Date] < GETDATE() +22)
Do be careful there.
You might want to look at what those dates are returning. It is preferred to use DATEADD instead of GETDATE() - 8.
The most important point though is that you are getting time values in there. Look at what this returns.
select getdate() - 8, getdate() + 22
Note that if you want times on the edge dates that are after the current time of day for the start day or before the current time on the upper end you are ok. However, that is not usually what is required.
You might want to look at this link for some common datetime routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2013 at 2:55 pm
Be careful here, getdate() is getting you the current date AND time. And that timestamp stays after adding or subtracting days.
Do you want to compare against seven and 21 days from THIS MOMENT? Or do you want all records starting 7 days ago at midnight and going to 21 days from now at midnight?
EDIT:Sean beat me to the obvious point
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply