get only Months and Year

Question

Hi Frnds i have a field in DB which contains format like below mm-yy/01 mm is month(06) yy is year(12) i wnt to know how to extract the only the mm-yy values from Datetime and combine it with the number value example is 06-12/01 06-12/02 06-12/03 010203 will increment. how can it be done. Pl reply aamir

Rahul Bhatt · Answer

Hi Get Month SUBSTRING ( Word  1  2 ) Get Year SUBSTRING ( Word  4  4 ) Your words some thing like 01-2012 / 01 01-2012 / 02 So you need to frst take month and year from above words After then make date using get month and year  Check with financial year start and end date If Data exist then increment no 1 Other wise reset counter Make your logic as following way DECLARE @intCOunt as INT = 0 select @intCOunt = COUNT (*) from Words where CAST ( SUBSTRING ( Word  1  2 ) + '/01/' + SUBSTRING ( Word  4  4 ) as DateTime ) BETWEEN CAST ( '04/01/' + CAST ( YEAR ( GETDATE ()) as varchar ) as DateTime ) AND CAST ( '03/31/' + CAST (( YEAR ( GETDATE ()) + 1 ) as varchar ) as DateTime ) IF ( @intCOunt = 0 ) BEGIN INSERT INTO Words ( Word ) values ( CONVERT ( char ( 2 ) GetDate () 101 ) + '-' + cast ( year ( getdate ()) as varchar ) + '/ ' + '01' ) END ELSE BEGIN INSERT INTO Words ( Word ) values ( CONVERT ( char ( 2 ) GetDate () 101 ) + '-' + cast ( year ( getdate ()) as varchar ) + '/ ' + CASE WHEN LEN ( @intCOunt + 1 ) = 1 THEN '0' + CAST (( @intCOunt + 1 ) as varchar ) ELSE CAST (( @intCOunt + 1 ) as varchar ) END ) END

Ram kumar · Answer

I hope this will helps you. SELECT DATENAME ( mm  article . Created ) AS Month  DATENAME ( yyyy  article . Created ) AS Year  COUNT (*) AS Total FROM Articles AS article GROUP BY DATENAME ( mm  article . Created ) DATENAME ( yyyy  article . Created ) ORDER BY Month  Year DESC

Santhosh Kumar Jayaraman · Answer

List strdate = the output of execution of sqlcommand either query or stored procedure. it can be of datatable also but the thing is u need to get the count of total records.. Then add records+1 to your insert. The query should be like this..pass actual parameters select * from test where Convert(intSUBSTRING(testsubject12)1) = (DateTime.Now.Month) and Convert(intSUBSTRING(testsubject42)1) =(DateTime.Now.Year)

Vulpes · Answer

Try something like this in your button Click handler: object temp = Session[mmyynn]; string now = DateTime.Now.ToString(MM-yy); if (temp != null) { string mmyynn = temp.ToString(); string mmyy = temp.Substring(0 5); if (mmyy == now) { int number = int.Parse(mmyynn.Substring(6)); number++; mmyynn = mmyy + / + number.ToString(D2); } else { mmyynn = now + / + 01; } Session[mmyynn] = mmyynn; } else { Session[mmyynn] = now + / + 01; } // code to write to database

Aamir Khan · Answer

dear santosh can u elaborate more on the List strdate=; part. regards aamir

Santhosh Kumar Jayaraman · Answer

This will be simple. List strdate = ;//Get all the date as list for current month and year. This you can achieve it in your query by validating it with parameter getdate() string month = Convert.ToString(System.DateTime.Now.Month); string year = Convert.ToString(System.DateTime.Now.Year); string y = year.Substring(2 2); string format = month + - + y + / +strdate.Count+1;

Aamir Khan · Answer

Dear friends i got the solution for following query string month = Convert.ToString(System.DateTime.Now.Month); string year = Convert.ToString(System.DateTime.Now.Year); string y = year.Substring(2 2); string format = month + - + y + /; but one more need arises as my data value is in format like 06-12/01...06-12/02...06-12/03...this how it should genrate the value the 010203..value should get incremented on each save click in DB....and as the month changes the format and number should also get revised like 07-12/01...07-12/02. pl reply

Vulpes · Answer

Try: int number = 1; // or whatever string combo = DateTime.Now.ToString(MM-yy) + / + number.ToString(D2);

Santhosh Kumar Jayaraman · Answer

Get the nvarchar field as string in webform. Then try to split the string and extract month and year. This is the code. string strdate = 06-12/01;//in your case the actual field from database table string[] splitdate = strdate.Split(new char[] { '-' '/' }); for (int i = 0; i { Console.WriteLine(splitdate[1] + - + splitdate[0] + /+i); } Console.Read(); Have attached working windows console solution also

Aamir Khan · Answer

guys in database the field is Nvarchar not as datetime. tell me how to get this mm-yy value i tried by using following code in web form string mm = Convert.ToString(System.DateTime.Now.Month); string yy = Convert.ToString(System.DateTime.Now.Year); string y = yy.Substring(1 3); string combo=mm+-+y; but not working

Sachin Kalia · Answer

Hi Aamir I have tried with this way .hope it will help you some where . SELECT RIGHT(CONVERT(VARCHAR(8) GETDATE() 5) 5) + '/01' as DateFormat SELECT SUBSTRING(CONVERT(VARCHAR(8) GETDATE() 5) 4 5) AS [MM-YY] I've used static 01 however you can you some increment variable. Thanks

Aamir Khan · Answer

Dear santosh the datatype in DB table is NVarchar(50) not as Datetime. coz i have to save in DB as 6-12/01

Santhosh Kumar Jayaraman · Answer

In your case it will be select Right(convert(varchar(4)Year(InsertDate)100)2) + '-'+convert(varchar(4)Month(InsertDate)100) to fetch year and month in YY/MM format

Santhosh Kumar Jayaraman · Answer

Please try this. The following query will get month and year as MM-YY format. If date is 12/25/2012 it will display as 12-12 select convert(varchar(4)Month(InsertDate)100) + '-'+Right(convert(varchar(4)Year(InsertDate)100)2) InsertDate from investments