Get Date From DateTime In SQL

Get Date From DateTime In SQL
Something I need to do from time to time is get just the date part of a datetime value in SQL. I found a cool way to do it on SQLJunkies today.

select convert(varchar,DateColumn,101)

The 101 means “mm/dd/yyyy” format, but there are a bunch of other codes you can use. 108 will return just the time “hh:mm:ss” for instance.

Update: 101 includes 4 digit year 'yyyy'. A code of 1 would apparently be "mm/dd/yy", according to user comments.

passing array to stored procedure

If you are trying to pass an array to a stored procedure then you can find your self in real difficulity.
This article explains a simple method for passing an array from your C# ASP .NET application to an SQL stored procedure by using a bit of dyanmic SQL.

MSSQL Limitations and Arrays
MSSQL is a favorite database platform for C# ASP .NET developers. For the most part, MSSQL has everything you need for your application. However, one particular missing feature is support for arrays as inputs to stored procedures. At first glance, one may think passing in a series of input parameters, with a pre-defined limit (such as var1, var, var3, var4) would be a suitable workaround. However, there is an easier way to accept an array of parameters.

Simple Method to Pass Array to a Stored Procedure - SQL Server Side

Consider the following simple method for defining the stored procedure using dynamic SQL. The array parameter is defined simply as a string and the input will be expected to be comma-delimited. By forming the sql dyanmically with the input string, we can query against the values in the array by using the IN command.

CREATE PROCEDURE [dbo].[GetData]
@MyCodes as varchar(500) = '', -- comma delimited list of codes, -: '''ABC'', ''DEF'', ''GHI'''
AS
BEGIN
DECLARE @query as nvarchar(500)

set @query = 'SELECT * FROM DATA WHERE Code IN (@p_MyCodes)'

exec SP_EXECUTESQL @query,
N'@p_MyCodes varchar(500)',
@p_MyCodes = @MyCodes
END
OR
ALTER PROCEDURE [dbo].[sessions_GetSchedule]
-- Add the parameters for the stored procedure here
@confGuid varchar(500),
@sIDlist varchar(500)

AS
BEGIN

DECLARE @SQL varchar(500)
SET NOCOUNT ON;

SET @SQL = 'SELECT sID, sName, sDate, sEndDate, sTime, sEndTime FROM sessions WHERE confguid='+@confGuid+' and sID in ('+@sIDlist+')'
EXEC(@SQL)

And towards your input side

=========test page to pass values as query sreing============================

Response.Redirect("NewSchedule.aspx?CART=" +TextBox1.Text + ";" +TextBox2.Text+"&confGuid="+DropDownList1.SelectedValue);
pass as query string

====page where schedular is displayed===========AAA001;AAA002;==================
IMPORTANT

if (Request.QueryString["Cart"] != "")
{
string confGuid = Request.QueryString["confGuid"];
string sID = Request.QueryString["CART"];
string NewsID = sID.Replace(";", "','");
string FinalsID = "'"+NewsID+"'";
string FinalConfGuid= "'" + confGuid+ "'";
Label1.Text = FinalsID;
ScheduleGeneral1.DataSource = BAL.BAL.GetSession(FinalsID,FinalConfGuid);
ScheduleGeneral1.DataBind();
}

-===============BAL================='''AAA001'',''AAA002'''======
public static DataSet GetSession(string spInput,string confGuid)
{

DataSet ds = DAL.DAL.DataSetDAL1("sessions_GetSchedule", spInput,confGuid);

return ds;
}

========================DAL==============================
public static DataSet DataSetDAL1(string spName, string spInput,string congGuid)
{
DataSet _Ds = new DataSet();
try
{
SqlConnection con = GetConnection();

SqlCommand cmd = new SqlCommand("sessions_GetSchedule", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);

cmd.Parameters.Add(new SqlParameter("@sIDlist", SqlDbType.VarChar));
cmd.Parameters["@sIDlist"].Value = spInput;

cmd.Parameters.Add(new SqlParameter("@confGuid", SqlDbType.VarChar));
cmd.Parameters["@confGuid"].Value = congGuid;

cmd.CommandType = CommandType.StoredProcedure;
con.Open();

da.Fill(_Ds);
con.Close();
if (_Ds.Tables[0].Rows.Count > 0)
{
return _Ds;
}

}
catch (Exception e)
{
e.Message.ToString(); ;
}

int count = 0;
//foreach (DataRow dr in _Ds.Tables[0].Rows)
if (_Ds.Tables[0].Rows.Count>0)
{
count = count + 1;
}


return _Ds;

}

Jquery preventDefault

In order to prevent the default behaviour for the regular function use

event.preventDefault();

Create Scheduler in Asp.net.C# or Databound Schedule controls

Databound Schedule controls

In order to display a schedular in asp.net, there is a third party tool avaliable.

Please go through this article to get details:

http://www.codeproject.com/KB/custom-controls/schedule.aspx


And Inorder to schedule an email through .net follow:

http://www.codeproject.com/KB/aspnet/Schedule_Task.aspx


sp for the scheduler

ALTER PROCEDURE [dbo].[sessions_NonTicketedScheduleDAY]
-- Add the parameters for the stored procedure here
@confGuid varchar(500),
@sDay varchar(500)
AS
BEGIN

DECLARE @SQL varchar(1000)


-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


--SET @SQL = 'SELECT sID, sName, sDate, sEndDate,sTime,sEndTime FROM sessions_NonTicketed WHERE confguid='+@confGuid+' and sdate between ('+@startDate+') and ('+@endDate+') '
SET @SQL = 'SELECT sID, sName, sDate, sEndDate,sTime,sEndTime,Category FROM sessions_NonTicketed WHERE confguid='+@confGuid+' and
Day(sdate)='+@sDay+''

--'SELECT sID, sName, sDate, sEndDate,sTime,sEndTime FROM sessions_NonTicketed WHERE confguid='+@confGuid+' and sdate >="'+convert(varchar(23),@startDate)+'"AND shippeddate <="'+convert(varchar(23),@endDate)+'"'
--'select * from orders where requireddate >="'+convert(varchar(23),@reqdate)+'" AND shippeddate >="'+convert(varchar(23),@shipdate)+'"'

EXEC(@SQL)
END

Also In case you wanna color codeing as per type its possible within this scheduler

Referencing an Assembly from Global Assembly Cache

In order to reference an seeembly from GAC. We have to have an assembly with a strong name, in case the assembly is not a strong named then you have to created a strong name for that assembly. follow this article for reference
http://www.kivela.be/index.php/2007/12/11/strong-signing-a-third-party-assembly-in-net/

After the strong name is assigned for any dll. If you try to reference any DLL at that point its reference is checked from GAC as per strong name or publick key token. If the strong name is matched with the SN for the DLL you are referenceing then you don't see the assemble in the bin of your project directory. else the DLL will be added to you BIN directory.

To add a reference

•In Solution Explorer, expand the project node to which a reference is to be added.
•Right-click the References node for the project and select Add Reference from the shortcut menu.
•To add a reference to a component or components, do the following:
•In the Add Reference dialog box, select the tab indicating the type of component to be referenced.
•In the top pane, select the component to be referenced, and then click the Select button.
Tip If the component you are looking for is not in the list, you may locate it using the Browse button.

The component referenced appears in the SelectedComponents pane of the dialog box.

•Repeat Step b for each additional component added.
•Click OK when references are all added.

To add assemblies to the Add References dialog box

•In Windows, click the Start button, click Run, then type regedit, to open the Registry Editor.
•Select the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\7.0\AssemblyFolders
•On the Edit menu, point to New, then, click Key.
•Enter a name for the key (for example, MyAssemblies).
•Select the (Default) value for the key that you just added.
•On the Edit menu, choose Modify.
•In the Edit String dialog box, select the Value data field and enter the full path to the folder where your assemblies are located.

To remove a reference

•In Solution Explorer, right-click the reference in the References node, and select Remove from the shortcut menu.
•By default, the Add References dialog box only lists assemblies that are in the Public Assemblies folder (Program Files\Microsoft Visual Studio .NET\Common7\IDE\Public Assemblies) or in the Global Assembly cache. You can add your own assemblies to the list by adding a registry key to specify their location

Strong signing a third party assembly in .net.?

Assigning a strong name to a third party DLL using your visual studio command prompt.

E.g.
Lets say the name of the third party DLL is myTest.dll.

Step 1: Dis-assemble the assembly, as we have to asign the strong name for DLL
ildasm myTest.dll /out:myTest.il


Create a Strong Name:
sn -k myTest.snk


Step 2: Re-Assemble using your strong-name key
ilasm myTest.il /res:myTest.res /dll /key:myTest.snk /out:myTestSN.dll

for verification you can use following command,
sn -vf myTestSN.dll