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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
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
 
PortletPaulCommented:
Set @EndDate  = DATEADD(dd, - (DAY(DATEADD(mm, 1, getdate() )) - 1), DATEADD(mm, 1, getdate() ))
0
 
PortletPaulCommented:
I'm sorry but WHY are you using varchar values?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
PortletPaulCommented:
?
@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
 
PortletPaulCommented:
OK
0
 
PortletPaulCommented:
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
All Courses

From novice to tech pro — start learning today.