Solved

Get Year, Month, beginning of next month

Posted on 2015-02-13
9
118 Views
Last Modified: 2015-02-13
I want to pass a string sample '02/01/2015' or any day of that month and get the following.

@enddate should return '03/01/2015'  (the beginning of next month)
@month   should return '02'
@year      should return '2015'

Declare @EndDate as varchar(12)
declare @month	  as varchar(2)
declare @year	  as varchar(4)



Set @EndDate 		= @startdate  > should be '03/01/2015' (the first day of next month)
Set @Month		        = @startdate  > should be '02'
Set @Year		        = @startdate  > should be '2015'


select @enddate
select @month 
select @year

select MONTH('02/01/2015')  < how can I return '02' instead of '2'
select YEAR('02/01/2015')    < this is ok   '2015'

Open in new window

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
  • 6
  • 3
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40609063
Set @EndDate  = DATEADD(dd, - (DAY(DATEADD(mm, 1, getdate() )) - 1), DATEADD(mm, 1, getdate() ))
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40609066
I'm sorry but WHY are you using varchar values?
0
 

Author Comment

by:VBdotnet2005
ID: 40609077
Hi PorletPaul,

It is fine. I am just test a small tmp table.
How about Month something like this ?
'0' + convert(char(10), DATEPART(MM, @startdate))   > I want it to return 02 instead of 2
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40609078
untested
declare @EndDate as varchar(12)
declare @month	  as varchar(2)
declare @year	  as varchar(4)



Set @EndDate  = CONVERT(varchar(12), DATEADD(dd, - (DAY(DATEADD(mm, 1, getdate() )) - 1), DATEADD(mm, 1, getdate() )), 101)
Set @Month    = CONVERT(varchar(2), getdate() , 101)
Set @Year     = CONVERT(varchar(4), getdate() , 111)


select @enddate
select @month 
select @year

Open in new window

0
 

Author Comment

by:VBdotnet2005
ID: 40609084
The beginning of next month '03/01/2015', not the first day of the month that is passing.

Set @EndDate  = DATEADD(dd, - (DAY(DATEADD(mm, 1, getdate() )) - 1), DATEADD(mm, 1, getdate() ))
0
 

Author Comment

by:VBdotnet2005
ID: 40609088
it is all good. Thank you very much
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40609090
?
@enddate should return '03/01/2015'  (the beginning of next month)

it returns see COLUMN_0
|   COLUMN_0 | COLUMN_1 | COLUMN_2 |
|------------|----------|----------|
| 03/01/2015 |       02 |     2015 |

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40609092
OK
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40609097
I do hope you are NOT using @enddate like this:

select * from some_table where a_datetime_field < @enddate;

If you are, it should be a datetime or date datatype

and don't ever assume MM/DD/YYYYY is "always safe to use" because it isn't
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

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