Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get Year, Month, beginning of next month

Posted on 2015-02-13
9
Medium Priority
?
122 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
  • 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

782 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