How to compare time values in SQL Server 2005

The following query filters the results set based on the time value only

Select * From <Table Name>

Where  Convert(Varchar(8),<DateTime Field Name>,8) >= ‘<Time Value>’

In the above query it will compare a time value with the format of,

           hh:mm:ss

If you don’t want the seconds portion of it simply reduce the length of the converted field

ie. Convert(varchar(5),<DateTime Field Name>,8)      this will only return a time value with the format of,

        hh:mm

Happy coding!!

How to get the number of days in the month (SQLServer 2005)

Create Function GetNumberOfDaysInTheMonth(@PDate DATETIME) Returns INT

AS

BEGIN 

RETURN CASE WHEN Month(@PDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

                                WHEN Month(@PDate) IN (4, 6, 9, 11) THEN 30

                                WHEN Month(@PDate)=2 THEN

                    CASE  WHEN (Year(@PDate)%4 = 0)  AND  (Year(@PDate)%100 != 0  OR  Year(@PDate)%400 = 0)                     

                    THEN 29

                    ELSE 28

                    END

 END

END

OLEDB DataShape Provider For SQL Server

Following is a connection string that enables DataShaping

string strConnectionString =  “Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=mySQLServerName;” +
                                                “Initial Catalog=myDatabase;User ID=myUsername;Password=myPassword”;

After making a successful connection you can fire following query to create a hierarchical record set

string strSQL = “SHAPE{Select <column1>,<column2>,… From <Table> Where <Conditions>} “+

                             ”  APPEND({Select <column1>,<column2>,… From <Table> Where <Conditions>} AS <Child Name> “+

                             ” RELATE  <PrimaryKey> TO <ForeignKey>)”;

Convert to numeric

I wrote following function in VBScripts to over come the fundamental problem that I faced when trying to convert any valid numeric value through an ADODB Recordset. Basically the VBScripts IsNumeric() is not working appropriately, it does not parse any valid numeric value. Maybe this is because of some internal issue with the VBScript engine, any way I wrote the following function to convert the given value to its corresponding numeric format. The NULLs, Alpha or Alpha numeric and Empty values will get converted to zero. The function will return either zero or the passed value, only if it can convert to any other numeric type without generating a runtime errors

Function ConvertTonumber(strVal)

on error resume next
  dim numberVal

  numberVal = 0

  if not isnull(strVal) then
     numberVal = cdbl(strVal)
  else
      numberVal=0
  end if

if Err.Number<>0 then
   numberVal=0
end if
ConvertTonumber = numberVal
End Function

How to do a SalesLogix6.2 Mail Merge

    Dim strPluginId,strField,strTable,strWhere,strContactId, strOpportunityId,strTemplate

    strContactId = Application.BasicFunctions.CurrentContactID
    strOpportunityId = Application.BasicFunctions.CurrentOpportunityID

   strTemplate = “<Template Name>”

    strField = “PLUGINID”
    strTable = “PLUGIN”
    strWhere = “TYPE = ’25’ AND NAME = ‘” & strTemplate & “‘ AND userid = ‘SYST00000001′”‘”‘ AND RELEASED = ‘T'”
    strPluginId = GetField(strField,strTable,strWhere)

    Application.BasicFunctions.MergeFromPlugin strPluginId, 2, strContactId, strOpportunityId

   Merge Modes

            1 – Fax

            2 – Word Document 

            3 – E-Mail