Wednesday, June 24, 2009

calculate hour minutes from milliseconds in oracle query

SQL QUERY:

SELECT
SUBSTR(to_char((39600/60/60),'00.00') , 0, INSTR(to_char((39600/60/60),'00.00'), '.', 1, 1)-1) hour,
to_char((SUBSTR(to_char((39600/60/60),'00.00'), INSTR(to_char((39600/60/60),'00.00'), '.', 1, 1),length(to_char((39600/60/60),'00.0'))) *60),'00') as Minutes
FROM dual;

OUTPUT

HOUR MINUTES
------ -------
11 00

1 rows selected

ANOTHER EXAMPLE

SELECT
SUBSTR(to_char((11700/60/60),'00.00') , 0, INSTR(to_char((11700/60/60),'00.00'), '.', 1, 1)-1) hour,
to_char((SUBSTR(to_char((11700/60/60),'00.00'), INSTR(to_char((11700/60/60),'00.00'), '.', 1, 1),length(to_char((11700/60/60),'00.00'))) *60),'00') as Minutes
FROM dual;

OUTPUT

HOUR MINUTES
------ -------
03 15

1 rows selected

Friday, June 5, 2009

FILL DATA USING ORACLE STORE PROCEDURE IN ASP.NET C#

PROCEDURE "GET_ALL_AREA"

(

ITEMS_CURSOR OUT SYS_REFCURSOR

)

is


BEGIN

OPEN ITEMS_CURSOR FOR SELECT AREAID,AREANAME,AREAPINCODE FROM AREA;


END;


DataSet ds = new DataSet();

OracleCommand command = new OracleCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = “Get_All_Area”;

command.Connection = conn.Get_Connection();

command.Parameters.Add("ITEMS_CURSOR", OracleType.Cursor ).Direction = ParameterDirection.Output;

OracleDataAdapter adpt = new OracleDataAdapter(Command);

adpt.Fill(ds, str_table);

Close_Connection();

public string Connection_String

{

get

{

return ConfigurationSettings.AppSettings["dbConnectionString"];

}

}

//Function For Open New Connection

public OracleConnection Get_Connection()

{

try

{

OracleConn = new OracleConnection(Connection_String);

OracleConn.Open();

}

catch (Exception ex)

{

throw ex;

}

return OracleConn;

}

//Close Connection

public void Close_Connection()

{

try

{

OracleConn.Close();

}

catch(Exception ex)

{

throw new Exception(ex.Message, ex);

}

}

CONVERT TEXT INTO INITCAP IN C#

public static string PropCase(string strText)

{

return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower());

}

ShareThis

Welcome

Welcome to Rajesh Prajapati, asp.net blog.
Here you can find some useful code and information about asp.net., c#, VB.net, SQL Server, Web Service, Web Designing etc