Get Year, Month, beginning of next month

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

VBdotnet2005Asked:
Who is Participating?
Advertise Here
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
PortletPaulfreelancerCommented:
Set @EndDate  = DATEADD(dd, - (DAY(DATEADD(mm, 1, getdate() )) - 1), DATEADD(mm, 1, getdate() ))
0
 
PortletPaulfreelancerCommented:
I'm sorry but WHY are you using varchar values?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Take the quiz
 
VBdotnet2005Author Commented:
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
 
VBdotnet2005Author Commented:
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
 
VBdotnet2005Author Commented:
it is all good. Thank you very much
0
 
PortletPaulfreelancerCommented:
?
@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
 
PortletPaulfreelancerCommented:
OK
0
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.