Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql - return date

Posted on 2014-10-22
2
Medium Priority
?
438 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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 setup several different housekeeping processes for a SQL Server.
Suggested Courses

578 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