Today’s Date in PL/SQL

by fwhagen Fri, 25 July 2014

Here’s the other one I forget all the time.  Although it’s much easier than T-SQL, to get today’s date in an Oracle query, use:

TRUNC(sysdate)

as in:

SELECT TRUNC(sysdate) from dual;

Remember:  Don't pass a date as a string.  Use the system variables.

Keywords:

Filed Under: DataBase | Oracle | Programming

Today’s Date in T-SQL

by fwhagen Wed, 19 February 2014

I forget this one all the time:  I need today’s date in a SQL query.  In C# it’s DateTime.Today, and in T-SQL it’s:

DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

So a simple select that will return all of todays records will look like this:

SELECT * FROM TableName WHERE CompletedDate >= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

Now I won't forget.

Keywords:

Filed Under: DataBase | Programming | SQL

Proper ASP.NET Database Calls

by fwhagen Mon, 20 August 2007

Last week, I posted an entry about a simple call to an MS-SQL database with a warning that it should only ever be used in a quick and dirty scenario.  By that I mean when it doesn't make sense to integrate or create a Data Access Layer or class to do the heavy lifting for you.

The advantages of creating classes to do the data calls behind the scenes are many, not the least of which is easier development in the long run.  Take a look at the following code:

    Query qEmplQuery = new Query(); 
    qEmplQuery.ConnectionSettings = ConfigurationManager.ConnectionStrings["EmployeeDB"]; 
    qEmplQuery.StoredProcedure = "DW.PKG_HRPORTAL.PRC_EmployeeInfo"; 
    qEmplQuery.AddParameter("P_EmployeeNumber", szEmplNum); 
    qEmplQuery.MinutesCached = 12 * 60; 
    DataSet dsEmplQuery = qEmplQuery.RetrieveDataSet("P_EmployeeInfo");

Much easier than even the "simple" call I posted earlier, isn't it.  The beauty of this is that it is also an Oracle call (database agnostic:  the same call for Oracle or MS-SQL makes for easier and standardized development) and is cached for 12 hours.  We also have a coding standard that only parameterized Stored Procedures are used for any kind of data access. 

But the most important advantage is that the Query class contains all of the complex coding needed to make this as efficient and "correct" as you know how and never have to think about it again.  This is basic abstraction and Object Oriented Programming fundamentals.  I have used Enterprise Library factories for my calls in Query because of the desire for its good caching and efficient data and network layer calls, but you can use whatever you want. 

In my next series of posts, I will present details of the Query class in order to help build your own.

Keywords: , ,

Filed Under: .NET | DataBase | Programming

TextBox

RecentPosts