ASP.NET with C# and SQL SERVER , some vb.net . asp.net examples, asp.net codes, asp.net programs, c# code, sql server queries, important codes.
Thursday, 18 October 2012
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");
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment