Subtract Month

Hi

I want to update a column of data type datetime in SQL Server 2000.
Let 

Col1
23/11/2009
31/12/2009
01/01/2010

i want to substract the 1 from month. i.e.

col1 shoul look like this

23/10/2009
31/11/2009
01/12/2009


Regard,
Muhammad Bilal


0
Utf
3/31/2010 11:16:01 AM
sqlserver.server 1327 articles. 0 followers. Follow

2 Replies
980 Views

Similar Articles

[PageSpeed] 47

Muhammad Bilal wrote:
> Hi
> 
> I want to update a column of data type datetime in SQL Server 2000.
> Let 
> 
> Col1
> 23/11/2009
> 31/12/2009
> 01/01/2010
> 
> i want to substract the 1 from month. i.e.
> 
> col1 shoul look like this
> 
> 23/10/2009
> 31/11/2009

Error: invalid date!

> 01/12/2009

Anyway, you are looking for the DATEADD function:

DATEADD (datepart , number, date )

where datepart is a code that indicates the unit of time you are adding. 
See:

http://msdn.microsoft.com/en-us/library/ms186819.aspx

As for the situation where the resulting date doesn't exist: "If 
datepart is month and the date month has more days than the return month 
and the date day does not exist in the return month, the last day of the 
return month is returned."
0
Harlan
3/31/2010 11:37:33 AM
Hi 

Take a look at DATEADD.

DECLARE @tbl TABLE (dt datetime)
INSERT INTO @tbl values (GETDATE())

UPDATE @tbl SET dt = DATEADD (dd , -1, dt )

SELECT * FROM @tbl

Jon

"Muhammad Bilal" wrote:

> Hi
> 
> I want to update a column of data type datetime in SQL Server 2000.
> Let 
> 
> Col1
> 23/11/2009
> 31/12/2009
> 01/01/2010
> 
> i want to substract the 1 from month. i.e.
> 
> col1 shoul look like this
> 
> 23/10/2009
> 31/11/2009
> 01/12/2009
> 
> 
> Regard,
> Muhammad Bilal
> 
> 
0
Utf
3/31/2010 12:05:01 PM
Reply:

Similar Artilces:

Can query calculate Data for each day of the month?
I want a query that will calculate data for each day of the month. For example, I have a query that will calculate A/P for any specific day of the month. This query will list all the open invoices for any particular day and a report will sum them up. I want another report that will print the running sum of A/P for each day of the month. Can this be done? To get daily sums on a report, you would have to pull all the details, then use grouping and sorting (combined with totals) to group by day and display only the group footer (with a total). -- Hope that helps! RBear3 .. "to...

Line numbers that are days of the month
I am trying to mimic a paper report (expense report) where the "line numbers" represent the day of the month. I currently have a single table to collect the data which includes ExpenseID, ExpenseDate, Travel, Phone, Gas etc. This has been a long standing report and I don't expect additional categories of expense to be added. My problem lies in one; numbering the lines with the number of the days of the month (I understand line numbering, just not when it may change as to number of days of the month) and listing the expenses on the same line as the correct number for th...

month & year format in two digits
Hi folks.... is this possible in excel that if I just type MM/YY without this "/" and it appears as MM/YY. For example if I type 0805 in the cell it should appears as 08/05 once entered. Help will be appreciated. Thanks Morphyus -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 http://www.cpearson.com/excel/DateTimeEntry.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Morphyus C via OfficeKB.com" <forum@OfficeKB.com> wrote in message news:5264F31820670@OfficeKB.com... > Hi folks.... > is this possible i...

Report of sales by month based on a cross tab query
Hi, I have a cross tab query that will provide sales by month for 2008. Right now it is sales for January08. As there are sales for future months, they will appear as well. I want to create a table that has all of the months listed out already. Right now, on the first day of the month, I go into the report and add the new month. I want it so that all month are listed which I have done. The problem is that when I run the report, I get an error that says "the Microsoft Jet does not recognize February as a valid field name. After today, there will be February data but no March ...

Cashflow record of purchase orders by supplier by month
Is there any way to maintain a cashflow record of Purchase orders placed in RMS by both supplier and month? If not is there and an ADD IN programme available? -- dave This is a multi-part message in MIME format. ------=_NextPart_000_0147_01C7F006.B23ED240 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Dave, Not exacting knowing what you're looking for, the built-in Purchase = Order report can show you open and closed POs for a range of time. = Manager | Reports | Quotes/Orders | Purchase Orders | set your filters. = If you want to ...

Subtracting Months
Hello all, I have a report with column headings that are dates. These are displayed in mm-yy format. The first column is todays date =date(). The next 11 columns need to display the previous 11 months. I had set it up as the second column being =date()-30 and the next column =date()-60, and so on and so forth. The problem is (which you may have already figured out) is that when we get to a certain point of the month and on certain months 30 days doesnt necessarilly equate to the previous month (such as on Jan 31st). In that event, I would have two Jan-08 columns (which I do not w...

Managed FOREX Account
Earn 3 % per month on average after commisions! Visit us at www.titancm.com or call us at 702-564-6699 for more information. ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- ...

Subtract dates in phone call entity
hi the phone call entity has two dates - actual start and actual end there is also a field called duration can someone please help me with a code which subtracts the actual end from the actual start to give the duration in minutes. thanks in advance regards Ridhima Hi Ridhima, Look at "Calculating durations" in http://www.stunnware.com/crm2/topic.aspx?id=JS12. Simply modify the script to fit your needs. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.co...

increase one month
Hello All, Is there any formula which can increase the above cell by one month. For example, The sheet looks like this, January =formula -------------> February =formula--------------> March .. .. .. =formula---------------> December Thanks If you just want a list of the months then you don't need a formula at all. Just enter January in a cell, hover the cursor over the bottom right-hand corner where the fill handle is, then when the cursor changes to a black cross-hair, left-click and hold the button pressed while you drag down the column to get a list of months. If yo...

$15,000 a month in 15 days
Earn $3500-$6000 a Week. Work with Top Money Earner 1 on 1 Not MLM Never Pick Up a Phone Not Predator or Coastal 100% automated 100% internet based business See how Average Joes are earning $20,000 their first month Get the details here www.0to20k.com ...

Monthly Charts
I'm creating a chart of activity each month that contains a 12-month history. It's usually a line chart of some activity per month for the last 12 months. What I want to do is delete the first month's cell, and shift cells left. Then add the new month to the end. When I do this, the chart does not include the new month's data. How do I format the source data for the chart to be a particular row of 12 cells, no matter what happens to those original cells. I don't want it to follow the cells to the left when I delete them! Thanks! "Chris Hagwood" <chrisha...

counting occurences of month on a specific worksheet
Hey all, Second time on here. You were all helpful the first time round. Here's the question: I have 5 worksheets in my workbook. Each worksheet represents one of our offices. Column A on each worksheet is a date in the following format: 03-May-10. I need to count how many times May appears on a specific worksheet. From what I've seen on other posts, I think I have to work with the =sumproduct function but I don't know where to enter the specific worksheet. Looking forward to your help. MM You want something like: =sumproduct(--(text(sheet1!a1:a100,"...

Need to subtract vat from gross amount.
Hi, I have created a calculator in excel for the purpose of quickl checking whether people who have employed us as a subcontractor hav deducted the correct amount of cis tax, but there is one thing i can get quite right. I want to be able to enter the gross amount in the top cell, and hav excel show me in the cell below what the original net amount of ou invoice was. For example: We did a contract for �400. The vat added took this up to �470. I want to be able to type in �470 in the top cell and excel will sho �400 in the cell below. I did discover that i could do this by using this fo...

Months #2
Hi, Is there a way (formula or whatever) to have the 12 months instead of typing them. I used Jan in cell B2 and Feb in cell C2, selected both cells and then used the mouse to copy them to the range D2:M2. Some of the months were Ok but not all of them? Can any one help? Khalil Handal You should be able to.... Select cells B2:C2 Click and hold on the black box in the lower right corner of C2 Then drag across to the right to fill in more months. What values do you get when you do that? *********** Regards, Ron XL2002, WinXP "Khalil Handal" wrote: > Hi, > Is there ...

Last week, month in rules ?
Is there any way I can use "last week" , "last month" in rules? It can be used when creating search but in the rules I can only find "received in a specific date span - before or after a date" so if I want to have a rule : delete all emails that are older than 3 months I can't really use this. Best Regards Rafal That's what Autoarchive is for. You could set it to delete anything older than 3 months in your email folder and run it weekly. Judy Gleeson [MVP Outlook] Acorn Training and Consulting www.acorntraining.com.au Everyone - turn on your A...

Convert weekly to monthly data
Hi, I have such question: My data is on weekly base in columns like this: date smth. ..... ... 27.08.02 471059 20.08.02 452789 13.08.02 465509 06.08.02 420657 30.07.02 430325 23.07.02 446067 16.07.02 486180 09.07.02 452964 .... ... And I want to organize it monthly on average. Could anyone help me with advise? Thanks, Svilen Hi Svilen Create a Pivot table. See http://www.cpearson.com/excel/pivots.htm Use "date" as row source and "smth" as data source. Rightclick the Date table header, choose the "group" menu, group by month. HTH. B...

Year/Month/Date Question
Is there a formula that can calculate years, months and days into a decimal? For example: 23 years, 6 months, 0 days would be 23.5 years. Depends on how your years, months and days are stored. Assuming they're in cells A1 (years), B1 (months) and C1 (days), then this formula would get you close: =A1+((B1*30)+C1)/365.25 That will actually return 23.49281, which if you round to 1 decimal place is 23.5 If you want to gty to get closer use B1*30.4375 instead of B1*30, since 365.25/12 = 30.4375 which you can "assume" is the average number of days in any single mont...

Subtract 1 Year from Query Date Range
I’m using: Between [Forms].[frmReport].[Start] And [Forms].[frmReport].[End] in criteria to input a date range into a query. The date is entered into the form “frmReport” in the format of 00/00/0000. Is there a way to modify the expression above so that it will subtract one year from the “Start” date and one year from the “End” date. I need to query one year earlier than the year entered into the form. Thanks -- Message posted via http://www.accessmonster.com Between DateAdd("yyyy", -1, [Forms].[frmReport].[Start]) And DateAdd("yyyy", -1, [Forms].[frmReport].[End...

monthly tabs missing in yearly calendar
I downloaded from vertex a yearly calendar and now the monthly tabs that enable me to look at month to month details have disappeared. I need some help to retrieve these and am not up with a lot of the jargon. Tools/ Options/ View/ Sheet tabs -- David Biddulph "Sandra" <Sandra@discussions.microsoft.com> wrote in message news:35425242-0B3E-490E-B473-17FE41E51042@microsoft.com... >I downloaded from vertex a yearly calendar and now the monthly tabs that > enable me to look at month to month details have disappeared. I need some > help to retrieve these a...

Conditional Formatting based on month name
I have a sheet where C2 contains the typed text of a month, i.e. February. Range C4:D14 contains actual dates spanning several months. I would like to highlight the cells that contain the month in C2. I've floundered around for several hours without a solution. Any help? P.S. I need C2 to remain text so it won't change until I change it. -- David Try: Select C4:D14 Click Format > Conditional Formatting Under Condition 1, make the settings as: Formula Is| =AND(TEXT(C4,"mmmm")=$C$2,C4<>"") Click Format button > Patterns tab > Light brown? >...

MONTHLY vs. daily compounded interest in a Loan...
It seems Money (2004) defaults to compounding mortgage loan interests daily. My bank compounds once monthly. As such, our balances are always off by a few dollars. Any suggestions? In microsoft.public.money, Aaron Segal wrote: >It seems Money (2004) defaults to compounding mortgage loan >interests daily. My bank compounds once monthly. > >As such, our balances are always off by a few dollars. > >Any suggestions? When you enter a loan, it is better to enter the payment data and let Money computer the rate. I'm not sure I understand how that will help. My situation ...

from $6 dollars to $6,000 in a month
Easy $$$ & it Works Turn $6 into $6,000 using PAYPAL!!! READING THIS COULD CHANGE YOUR FINANCIAL LIFE! IT REALLY DOES WORK! You have most likely seen or heard about "The Letter" $6.00 program that was recently televised on 20/20, OPRAH WINFREY and an article published in the Wall Street Journal on 6/16/2001. If not, here it is!!! Everyone has heard about "PayPal" (if you haven't you will soon) and, when I came across this concept I knew it would work because, as a member of PayPal, I had already experienced their efficiency and excellent standing. I've always th...

Display Month in a label driven from data series
Can an independent label have a formula reference that looks at a particular cell and displays that value?. I am importing data on a monthly basis, that over rights existing data (the existing data is copied and stored for refernece) and have a chart which updates for that months data. I can automate the printing of the chart but what I wish is that some label on the chart shows the month that the data is for. The label looks at a cell and shows that value. If it can, what is the format for the expression ? in the label, better still where do I start, I'm thinking along the lines ...

Create custom outline numbering in Word (like names of months).
To create an agenda of activities by month, I have to turn off the bulleting to type in the names of the months and then turn it on again to list the activities. I would like to be able to create a custom list that Word could pull from when I select my numbering style (so it would appear with "1, 2, 3" and "I, II, III" and the rest). I would then be able to create custom lists for the months of the year, or department names within our division, or employee names within a department, to make agendas that much easier to create. ---------------- This post...

Report to show totals for each day of month
Greetings, I have an Excel spreadsheet that I use to capture: Column A) Day of the month (1-May, 2-May, etc.) Column B) How many clients were in residence on each day (Occupancy) Column C) Total Capacity (27) Column D) Shows a "1" if the Occupancy is 90% or more of the Total Capacity, else "0" The Totals row shows: Column B) The average Occupancy for the month Column D) Totals the times there is a "1" in Column D Is there a way for me to capture this data in a Report? I've got a query set up that uses these expressions: For AdmitDate: <=[Dat...