datetime manipulation in asp.net and sql server

-----------------------------------------------------------------------------------------------

1.Convert(varchar(10),date,105) AS Date (--In cs File--)====> 17-11-2009

2.Convert.ToDateTime(txt_Dissue.Text) (--In cs File--)=====>Sending date with converting datetime

3.SET @lastdate=convert(datetime,getdate(),105) (--In SP --)====> 17-11-2009

4.set @date=getdate() 11/18/2009  (--In SP --)====> 11/18/2009 4:40:00 PM

5.declare @ycheck int set @ycheck=year(getdate()) (---SP--) ===> Getting Year Name

6.declare @Month_Name varchar(15) set @Month_Name = Datename(Month,getdate()) (---SP--) ===> Getting Month Name

7.declare @Month_No varchar(15) set @Month_No = Datepart(Month,getdate()) (---SP--) ===> Getting month No. //0r

8.declare @mcheck int set @mcheck=month(getdate()) (---SP--) ===> Getting month No.

9.declare @compDate set @compDate=dateadd(month,@addDate,@investDate) (---SP--) ===> Adding Date

10.SET @NextDate=dateadd(month,- 1,@date)(--IN SP--)====>  GET One Less MONTH

11.declare  @count set @count=datediff(month,@investDate,@compDate) (---SP--) ===> Getting Difference between two dates

12.DateTime startDate = new DateTime(2005, 2, 1, 3, 4, 12, 56);

13.DateTime endDate = new DateTime(2005, 12, 12, 4, 30, 45, 12);====>// End date

14.TimeSpan diffDate = endDate.Subtract ( startDate );====>// Time span

15.txt_DOB.Text =
  ddl_b1.SelectedValue.ToString() + "/" +
  ddl_b2.SelectedValue.ToString() + "/" +
  ddl_b3.SelectedItem.Text; ====> (Assigning Date in Textbox from DDL [02-12-2009])

16.dropfill();
   public void dropfill()
    {
        for (int i = 1900; i <= DateTime.Now.Year; i++)
        {
            ddl_b3.Items.Add(i.ToString());
            nddl3.Items.Add(i.ToString());
        }
    } ====> (For DDL Date Binding)

17. '" + Convert.ToDateTime(DateTime.Now) + "'  ====> Both Date & Time (in cs)

18. '"+DateTime.Now.ToShortDateString()+"'  ====> Only For Date (in cs)

19. '"+DateTime.Now.ToShortTimeString()+"'  ====>Only For Time  (in cs)

20. (DOJ <=dateadd(month,3,DOJ)))    ====>Add Month (---In sp--)

21.set @newdate=dateadd(day,7,@d)   ====>Add 7 Days in date (---In sp--)

22. txt_DOJ.Text = DateTime.Now.ToString("dd/MM/yyyy"); =====>07/04/2010  (in cs)

23.         string MONTH= txtDate.Text.Substring(0, 2);
            string DATE= txtDate.Text.Substring(3, 2);
            string YEAR= txtDate.Text.Substring(6, 4);
           
string billdate = month + "/" + date + "/" + year;   =====>07/04/2010  (in cs)

24.     --------------- To Dra (day,month) in cs--------------------
     
        int dt = con.select_data_scalar_int("select day(getdate())");
        int dt2 = con.select_data_scalar_int("select month(Max(Date)) from tbl_IncentiveIncome");
        int dt3 = con.select_data_scalar_int("select month(getdate())");

-------------------------------------------------------------------------------------------------

25. declare @date as Varchar(max)
    set @date =convert(Varchar, {fn now()},100) (--In SP --) =====> Apr 14 2010 12:34PM    

26.     txt_DOJ.Text = con.select_data_scalar_string("select convert(varchar(10), getdate(), 103)");  (--In CS --) =====>  26/04/2010

27.SELECT DATEADD(yyyy, 1, GETDATE()) AS Date    (--In cs --)=======>5/8/2011 11:40:24 AM

28.SELECT DATEADD(mm, 1, GETDATE()) AS Date   (--In cs --)=======>6/8/2011 11:40:24 AM

29.SELECT DATEADD(dd, 1, GETDATE()) AS Date   (--In cs --)=======>5/9/2011 11:40:24 AM

30.lblmonth.Text = DateTime.Now.ToString("MMMM");(--In cs --)====To get Name of month

31.SELECT SUM(JoiningAmount) AS TotalAmount FROM EndUserAddress WHERE (CONVERT(Varchar, DOJ, 105) BETWEEN '01-06-2010' AND '01-06-2010')

32.SELECT SUM(JoiningAmount) AS TotalAmount FROM EndUserAddress WHERE (DOJ BETWEEN '5/31/2010 5:14:45 PM' AND '6/1/2010 11:51:40 AM')

33.SELECT DATEDIFF(YY, DOB, DOJ) AS Difference FROM  EndUserAddress WHERE(MainID = 'FF1000')========>Dra Year between two dates ........  

34.SELECT DATEDIFF(MM, DOB, DOJ) AS Difference FROM  EndUserAddress WHERE(MainID = 'FF1000')========>Dra Month between two dates ........  

35.SELECT DATEDIFF(DD, DOB, DOJ) AS Difference FROM  EndUserAddress WHERE(MainID = 'FF1000')========>Dra Days between two dates ........

36.SELECT DATEDIFF(Second, DOB, DOJ) AS Difference FROM  EndUserAddress WHERE(MainID = 'FF1000')========>Dra Seconds between two dates ........  

37.SELECT DATEDIFF(Minute, DOB, DOJ) AS Difference FROM  EndUserAddress WHERE(MainID = 'FF1000')========>Dra Minutes between two dates ........  

38.SELECT DATEDIFF(Hour, DOB, DOJ) AS Difference FROM  EndUserAddress WHERE(MainID = 'FF1000')========>Dra Hours between two dates ........  

39. string date1 = con.select_data_scalar_string("select DOB from EndUserAddress where MainID='FF1000'");
    string date2 = con.select_data_scalar_string("select DOJ from EndUserAddress where MainID='FF1000'");

    DateTime startdate = Convert.ToDateTime(date1);
    DateTime enddate = Convert.ToDateTime(date2);

    TimeSpan diff = enddate.Subtract(startdate);   =====================> CS Code

int Y = (diff.Days/365);
        int M = (diff.Days / 30);

40.<asp:BoundField DataField="dateOn" HeaderText="dateOn"  DataFormatString="{0:dd-MM-yyyy}" /> ========> 11-08-2010 ...Changing date format at design Time...

41. txt_dateFrom.Text= GridView1.Rows[e.NewEditIndex].Cells[10].Text.Trim().Substring(0,10); ========> 11-08-2010 ... It reduce Time  in date(12:00:00 AM)

42. DateTime dt1 = DateTime.Parse(txt_grat_doj.Text);
    DateTime dt2 = DateTime.Parse(txt_grat_doretire.Text);
           
            if (dt1 < dt2)
            {

   } ================> We can fire Conditions two textbox dates...........



=============================================================================================
43. DateDiff(startdate,enddate);
         
string resu = ViewState["res"].ToString();




private void DateDiff(DateTime startDate, DateTime endDate)
        {
          string timeStr = string.Empty;   int yr = 0;   int mth = 0;   int days = 0;
             
            TimeSpan ts = new TimeSpan();
             
            ts = endDate.Subtract(startDate);
             
            yr = (ts.Days/365);
             

            do
           
            {      

            for(int i=0; i <= 12; i++)    
           
            {        

            if(endDate.Subtract(startDate.AddYears(yr).AddMonths(i)).Days > 0)  
               
            {            

            mth = i;        

            }        
            else        

            {            

            break;        

            }
                 
            }      

            if(mth > 12)        

             yr = yr + 1;  

            }

            while

            (mth > 12);  

            days = endDate.Subtract(startDate.AddYears(yr).AddMonths(mth)).Days;
               
            if(yr > 0)              

            timeStr += yr.ToString() + "year ,";  

            if(mth > 0)              

            timeStr += mth.ToString() + "Month ,";  
           
            if(days > 0)              

            timeStr += days.ToString() + "Days ,";

            string Result = timeStr;
            ViewState["res"] = Result.ToString();

   
    }
==================================================================================================
44.
   SELECT    
      CASE WHEN MONTH(Date) = 1 THEN 'Jan'
      WHEN MONTH(Date) = 2 THEN 'Feb'
WHEN MONTH(Date) = 3 THEN 'Feb'
WHEN MONTH(Date) = 3 THEN 'Feb'
END AS Month
FROM  tablename   (---SP--) ===> To Set Month Name

45.SELECT CONVERT(char(2), DATEPART(hour, GETDATE())) + ':' +
CONVERT(char(2), DATEPART(mi, GETDATE())) + ':' +
CONVERT(char(2), DATEPART(s, GETDATE()))  AS Time  ========> Get Only Time From (Without AM/PM) Date (in SQL)

46.select Convert(varchar,getdate(),8) as Time ========> Get Only Time With Seconds(Without AM/PM) From Date (in SQL)

47.SELECT RIGHT(CONVERT(varchar(30), GETDATE(), 121), 12)  ========>AS time Get Only Time With MiliSeconds (Without AM/PM) From Date (in SQL)

48. SELECT     DATENAME(weekday, GETDATE()) AS Expr1 ======== Get Week day    


49.string DOB = Convert.ToDateTime(txt_Editdob.Text).ToString("MM/dd/yyyy");