Solved

sql - return date

Posted on 2014-10-22
2
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 66

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how the fundamental information of how to create a table.

688 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