SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 current date minus 3 months
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vasu4us
Posting Yak Master

102 Posts

Posted - 01/18/2008 :  11:06:12  Show Profile  Reply with Quote
I have a requirment where i need to check 2 columns yyyy and mm which contain year and month.
this condetion is for a where clause to get the data between current date goint back to 3 months.I came up with this but its problem when year changes

WHERE YYYY + MM BETWEEN CAST(CONVERT(VARCHAR(4),GETDATE(),112)+ right('00' + convert(varchar,(month(getdate())-3)),2) AS INT) AND
CAST(CONVERT(VARCHAR(6),getdate(),112) AS INT)

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 01/18/2008 :  11:15:42  Show Profile  Reply with Quote
where (yyyy <=YEAR(GETDATE()) AND YEAR >YEAR(DATEADD(mm,-3,GETDATE())))
AND (mm<=MONTH(GETDATE()) AND mm>MONTH(DATEADD(mm,-3,GETDATE())))
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 01/18/2008 :  12:51:40  Show Profile  Reply with Quote
thank you visakh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/18/2008 :  16:28:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Hmmm... What about 2007-02 and 3 months back? Or 2007-11 and 3 months back?

WHERE 12 * yyyy + mm >= 12 * YEAR(GETDATE()) + MONTH(GETDATE())



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 01/22/2008 :  14:25:06  Show Profile  Reply with Quote
now i am running into a new issue with the where condetion. when ther is a changein year the first condetion ie yyyy(year)gives 2007 and 2008 and the second condetion mm(month)is giving 1 and 10. which means in 2007 1st to 10th month and 2008 1st 10th month.
My requirment is to get data for running three months ie oct this date till today. any suggestions
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/22/2008 :  18:49:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you for real?
I posted a solution for you 4 days ago.

WHERE 12 * yyyy + mm >= 12 * YEAR(GETDATE()) + MONTH(GETDATE())



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

vasu4us
Posting Yak Master

102 Posts

Posted - 01/23/2008 :  12:42:02  Show Profile  Reply with Quote
Gr8 Peso I could never guess something like that, how could you think so differently.
i was all th etime stuck with

where int(ltrim( rtrim("SQLUSER"."BTT120".YYYY)) CONCAT ltrim( rtrim("SQLUSER"."BTT120".MM))) between
int(ltrim( rtrim(char(YEAR(CURRENT DATE)))) concat right(ltrim(rtrim('00' concat char(month(CURRENT DATE)))),2)) and
int(ltrim( rtrim(char(YEAR(CURRENT DATE- 3 months)))) concat right(ltrim(rtrim('00' concat char(month(CURRENT DATE- 3 months)))),2))

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/24/2008 :  02:47:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I have learned from MVJ.
Dates are nothing more than a sequence number from a certain fixed point in history.
In SQL Server it just happens to be 19000101 by default.
You can temporarily change this date to any toher date to fit your purpose.

In my example I changed the "fixed point" to January 1, 0000.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

yankee_doodle
Starting Member

USA
1 Posts

Posted - 01/21/2011 :  12:04:17  Show Profile  Reply with Quote

where YOURDATECOLUMN between dateadd(month,-3,getdate())
and getdate()
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/21/2011 :  12:18:05  Show Profile  Reply with Quote
quote:
Originally posted by yankee_doodle


where YOURDATECOLUMN between dateadd(month,-3,getdate())
and getdate()

Might want to read the original post again.
Go to Top of Page

maughn
Starting Member

Philippines
3 Posts

Posted - 05/01/2011 :  21:12:06  Show Profile  Reply with Quote
can you help me the same problem. i have a given date column and i will subtract exactly 1 month from the given month.

thanks.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 05/01/2011 :  21:53:48  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
You mean same date 1 month ago?
dateadd(mm,-1,dte)
start of previous month?
dateadd(mm,datediff(mm,0,dte)-1,0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

maughn
Starting Member

Philippines
3 Posts

Posted - 05/01/2011 :  22:26:48  Show Profile  Reply with Quote
yes same date 1 month ago.

thanks nigelrivett
Go to Top of Page

jblanch62410
Starting Member

3 Posts

Posted - 08/24/2011 :  14:52:37  Show Profile  Reply with Quote
Same issue but diffrent ranges I need to know how to get the query to do a current and 12 months past time fram so ex.201108 through 201008. Then a second query will do a 3 month comparison as in ex.201107,201108 and 201008 hopefully in that order. I could do both in access using datediff but it does not seem to work in sql05. I would like to move the access querys to sql for more efficiency and less id 10t errors.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 08/25/2011 :  02:04:11  Show Profile  Reply with Quote
it should be below i think

where datefield >= dateadd(yy,datediff(yy,0,getdate())-1,0)
and datefield <dateadd(mm,datediff(mm,0,getdate())+1,0)
)
 



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jblanch62410
Starting Member

3 Posts

Posted - 08/25/2011 :  07:45:30  Show Profile  Reply with Quote
I have tried what you posted above. It gives me a datetime conversion error. The creator of the database that I am grabbing data from created the table with the year and date fields seperated as varchar(255). I am a newbie to SQL but have done access for years. and I am able to do it access but am looking to simplify that app and push most of the work to the server.
I used this to accomlish it after joining the date fields with a "/" seperator.And telling it I only want 1-13
DateDiff("m",Now(),[qrydatejoin]![datejoin])+13.

I am not sure if that is the issue and I should just rebuild the tables. If thats the case I would have to look into how to have the table self populate with the month/year and in another column how many working days there are in a month. or I am in for a lot of typing. But I might be missing the point and i can evaluate a varchar. Thanks for all of your time!
Go to Top of Page

jblanch62410
Starting Member

3 Posts

Posted - 08/25/2011 :  09:17:27  Show Profile  Reply with Quote
Thank you all for the time in trying to help with this issue. I have resolved by taking my own advise and manually adding that actual date field. then using the datediff function as described above. It wokes great. Again I do appreciate your time.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000