September 14, 2015 at 2:51 pm
Thanks Scott,
How do I fix this code:
AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20
I only need getdate() -21, but that's giving me blank output
September 14, 2015 at 2:59 pm
SQLPain (9/14/2015)
Thanks Scott,How do I fix this code:
AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20
I only need getdate() -21, but that's giving me blank output
Use DATEADD
😎
AppDetails.DateDetailDisposed BETWEEN DATEADD(DAY,-25,getdate()) AND DATEADD(DAY,-20, getdate())
September 14, 2015 at 3:07 pm
Thanks Errikur,
I only want results from past 21 days, I tried this also:
AppDetails.DateDetailDisposed = DATEADD(DAY,-21,getdate())
Still gives me a blank result set
September 14, 2015 at 3:07 pm
SQLPain (9/14/2015)
Thanks Scott,How do I fix this code:
AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20
I only need getdate() -21, but that's giving me blank output
WHERE ... AND
AppDetails.DateDetailDisposed >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 25, 0) AND
AppDetails.DateDetailDisposed < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 19, 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 14, 2015 at 3:17 pm
SQLPain (9/14/2015)
Thanks Errikur,I only want results from past 21 days, I tried this also:
AppDetails.DateDetailDisposed = DATEADD(DAY,-21,getdate())
Still gives me a blank result set
Missed the 21 days:-P
😎
AppDetails.DateDetailDisposed BETWEEN CONVERT(DATE,DATEADD(DAY,-25,getdate()),0) AND CONVERT(DATE,getdate(),0)
Note, convert to date if you are not using the time part.
September 14, 2015 at 3:29 pm
Thanks Again, but this one is giving me all the results from the last 25 days till today...
why do we need a between clause if I only need records from (08/24/15) !!!
September 14, 2015 at 3:40 pm
SQLPain (9/14/2015)
Thanks Again, but this one is giving me all the results from the last 25 days till today...why do we need a between clause if I only need records from (08/24/15) !!!
Change 25 to 21
😎
September 14, 2015 at 3:46 pm
Yes and that would give results from 0/81/15 till today....No help
September 14, 2015 at 3:56 pm
Sorry, slightly hurried and inaccurate
😎
CONVERT(DATE,AppDetails.DateDetailDisposed,0) = CONVERT(DATE,DATEADD(DAY,-21,getdate()),0)
September 14, 2015 at 4:09 pm
This happens to be a view query that needs to be run every night, needs to be converted to an excel file and email the attachment to couple of people.
Does any know the process I need to follow?
Do I have to uses SSIS ? or can I do it through the export wizard? and then set it up as SQl server job?
September 14, 2015 at 10:49 pm
SSIS, SSRS, maybe Powershell?
You can use the Export wizard to create an SSIS package, you'd need to modify the package to add the email & scheduling afterwards.
SSRS can generate the letters themselves if that's what you need.
September 14, 2015 at 11:03 pm
SQLPain (9/14/2015)
This happens to be a view query that needs to be run every night, needs to be converted to an excel file and email the attachment to couple of people.Does any know the process I need to follow?
Do I have to uses SSIS ? or can I do it through the export wizard? and then set it up as SQl server job?
There are quite few ways of doing this, suggest you post this as a new question as it is entierly unrelated to the original post.
😎
September 15, 2015 at 5:27 am
Eirikur Eiriksson (9/14/2015)
Sorry, slightly hurried and inaccurate😎
CONVERT(DATE,AppDetails.DateDetailDisposed,0) = CONVERT(DATE,DATEADD(DAY,-21,getdate()),0)
If your DateDetailDisposed column is a datetime data type, you might run into a performance problem here. Applying a function to a column in the table means that the function has to be applied to every row in the table before filtering out the rows. Using a date range to calculate the date range instead of converting the column in the table is generally a better way to go about it.
AND AppDetails.DateDetailDisposed >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)
AND AppDetails.DateDetailDisposed < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 20, 0)
September 15, 2015 at 9:01 am
Ed Wagner (9/15/2015)
Eirikur Eiriksson (9/14/2015)
Sorry, slightly hurried and inaccurate😎
CONVERT(DATE,AppDetails.DateDetailDisposed,0) = CONVERT(DATE,DATEADD(DAY,-21,getdate()),0)If your DateDetailDisposed column is a datetime data type, you might run into a performance problem here. Applying a function to a column in the table means that the function has to be applied to every row in the table before filtering out the rows. Using a date range to calculate the date range instead of converting the column in the table is generally a better way to go about it.
AND AppDetails.DateDetailDisposed >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)AND AppDetails.DateDetailDisposed < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 20, 0)
Actually, test it. Doing a CONVERT or CAST on a DateTime value DOES NOT always result in the Optimizer not being able to use an index on that column.
September 15, 2015 at 9:28 am
Lynn Pettis (9/15/2015)
Ed Wagner (9/15/2015)
Eirikur Eiriksson (9/14/2015)
Sorry, slightly hurried and inaccurate😎
CONVERT(DATE,AppDetails.DateDetailDisposed,0) = CONVERT(DATE,DATEADD(DAY,-21,getdate()),0)If your DateDetailDisposed column is a datetime data type, you might run into a performance problem here. Applying a function to a column in the table means that the function has to be applied to every row in the table before filtering out the rows. Using a date range to calculate the date range instead of converting the column in the table is generally a better way to go about it.
AND AppDetails.DateDetailDisposed >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)AND AppDetails.DateDetailDisposed < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 20, 0)
Actually, test it. Doing a CONVERT or CAST on a DateTime value DOES NOT always result in the Optimizer not being able to use an index on that column.
I'd suggest avoiding any conversion of a table column for comparison unless you absolutely have to, even if for a specific case it works in a given query plan. It's just poor technique.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply