 | |
SQL Query Get Data a Month Old
microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
SQL Query Get Data a Month Old -
08-07-2007
, 03:10 PM
#2
| |||
| |||
|
|
Does this part of the query look correct? It's formated so that it doesn't use the time feature and zero's it out to midnight... where datecreated between DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE() order by datecreated |
including the current day. However, you might consider using
DATEADD/DATEDIFF to remove the time portion of datetime:
DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))
Quote:
|
how would i create a query that returns all the rows with the exception of the past month....? thank you! |
WHERE
datecreated <
DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), GETDATE()
To return only data older than the start of last month:
WHERE
datecreated <
DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote
Quote:
|
Does this part of the query look correct? It's formated so that it doesn't use the time feature and zero's it out to midnight... where datecreated between DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE() order by datecreated how would i create a query that returns all the rows with the exception of the past month....? thank you! |
#3
| |||
| |||
|
|
If you want to return only data older than the last 28-31 days, use '<': WHERE datecreated DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), GETDATE() |
WHERE
datecreated <
DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda (AT) nospam-online (DOT) sbcglobal.net> wrote
Quote:
|
Does this part of the query look correct? It's formated so that it doesn't use the time feature and zero's it out to midnight... where datecreated between DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE() order by datecreated This looks OK to me as long as you want the last 28-31 days or so of data, including the current day. However, you might consider using DATEADD/DATEDIFF to remove the time portion of datetime: DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) how would i create a query that returns all the rows with the exception of the past month....? thank you! If you want to return only data older than the last 28-31 days, use '<': WHERE datecreated DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), GETDATE() To return only data older than the start of last month: WHERE datecreated DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) -- Hope this helps. Dan Guzman SQL Server MVP "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message news:uNJ3P5S2HHA.3640 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Does this part of the query look correct? It's formated so that it doesn't use the time feature and zero's it out to midnight... where datecreated between DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE() order by datecreated how would i create a query that returns all the rows with the exception of the past month....? thank you! |
#4
| |||
| |||
|
|
If you want to return only data older than the last 28-31 days, use '<': WHERE datecreated DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), GETDATE() Oops - copy/paste error. This should have been: WHERE datecreated DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))) -- Hope this helps. Dan Guzman SQL Server MVP "Dan Guzman" <guzmanda (AT) nospam-online (DOT) sbcglobal.net> wrote in message news:O6AtZ3W2HHA.4680 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Does this part of the query look correct? It's formated so that it doesn't use the time feature and zero's it out to midnight... where datecreated between DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE() order by datecreated This looks OK to me as long as you want the last 28-31 days or so of data, including the current day. However, you might consider using DATEADD/DATEDIFF to remove the time portion of datetime: DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) how would i create a query that returns all the rows with the exception of the past month....? thank you! If you want to return only data older than the last 28-31 days, use '<': WHERE datecreated DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), GETDATE() To return only data older than the start of last month: WHERE datecreated DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0) -- Hope this helps. Dan Guzman SQL Server MVP "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message news:uNJ3P5S2HHA.3640 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Does this part of the query look correct? It's formated so that it doesn't use the time feature and zero's it out to midnight... where datecreated between DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE() order by datecreated how would i create a query that returns all the rows with the exception of the past month....? thank you! |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.