Solved

sql - return date

Posted on 2014-10-22
2
407 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:
Scott Pletcher 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 61
insert wont work in SQL 14 22
Microsoft Access Write errors seem to be caused by bit fields 4 36
sql server query 6 9
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 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