Solved

sql - return date

Posted on 2014-10-22
2
397 Views
Last Modified: 2014-10-23
I want below to return the date of a month ago or whatever the @numofmonth is.

declare @Date as datetime
DECLARE @NumOfMonth AS int
set @NumOfMonth = 1
set @Date = (dateadd(dd,-datepart(dd,getdate())+ 1,dateadd(mm,-@NumOfMonth,getdate())))
print @date

This returns Sep  1 2014 11:39AM
0
Comment
Question by:VBdotnet2005
2 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40397903
So how does there year come into play?   Assuming the YEAR(GETDATE()),
DECLARE @NumOfMonth AS int = 1

Declare @dt date = CAST(GETDATE() as date) 

declare @dt_passed_month as date 

SET @dt_passed_month = CAST(CAST(YEAR(@dt) as char(4)) + '-' + RIGHT('0' + CAST(@NumOfMonth as varchar(2)),2) + '-01' as date) 

PRINT DATEADD(m, -1, @dt_passed_month)

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 40397961
Don't mess with strings, they will slow you down and depend on regional settings.  Instead, use straight "date math" ... which is simpler and more consistent anyway once you get used to it :-).


DECLARE @Date as datetime
DECLARE @NumOfMonth AS int

SET @NumOfMonth = 1
SET @Date = DATEADD(MONTH, @NumOfMonth - 1, DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0))

PRINT @date
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now