Date Ranges Start and End ASE 12 T-SQL

— Getting month ranges

–First you need to figure out the current date:

declare @today datetime
select @today=getdate()
–Let’s start with the current month:

–Make the Assumption for examples I am working with a date of January, 30th. So the first day of the month is January, 1st.
–Unfortunately, you cannot directly set the day to 1. But you can extract the day from the date, in our case 30 and 1 is 30 – (30 – 1), so:

select dateadd(dd,-(day(@today)-1),@today)
–This will return: Jan 1 2013 1:49PM

–So basically we have the right date but for comparison purposes, we’d rather have midnight than 1:49pm.
–In order to do it, you need to convert it to a date string and back to a datetime:

select convert(datetime, convert(varchar(10),dateadd(dd,-(day(@today)-1),@today),101))
–Now we get: Jan 1 2013 12:00AM
–if you’re only interested in a string containing the date, just drop the outer convert:
select convert(varchar(10),dateadd(dd,-(day(@today)-1),@today),101)
–Use another format than 101 if needed. The complete list of date conversion formats can be found here. For example, for the German date format, use 104 (

–MULTIPE PARTS HERE — Now let’s get the last day of the current month. This is basically the day before the first day of next month.

–So first let’s get the first day of next month.
— This is actually just 1 month later than the first day of the current month:

select convert(varchar(10),dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today)),101) as ‘First day of next month.’ — Part one of last day of current month
–This returns: 02/01/2013

–Now let’s just substract one day and we’ll get the last day of the current month:

select convert(varchar(10),dateadd(dd,-1,dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today))),101) as ‘last day of the current month’
–This returns: 01/31/2013

–Since we already have the first day of next month, let’s get the last day of next month.
— This is basically the same again but instead of adding 1 month, you add 2 months:

select convert(varchar(10),dateadd(mm,2,dateadd(dd,-day(@today),@today)),101) as ‘last day of next month’
–This returns: 02/28/2013

–Now let’s tackle the previous month.
— The first day of last month is basically the first day of the current month minus 1 month:

select convert(varchar(10),dateadd(mm,-1,dateadd(dd,-(day(@today)-1),@today)),101) as ‘first day of last month’
–This returns: 12/01/2012

–And then the last day of previous month. It is one day before the first day of the current month:
select convert(varchar(10),dateadd(dd,-(day(@today)),@today),101) as ‘last day of previous month’
–This returns: 12/31/2012

Powershell convert table colm index to letters for excel

# Convert columns Interger values to Letters for excel column range selections
# 1 based values, if your array is not zero based subtract 1 from index first
function ExcelColumnIndexToName {
[Parameter(Mandatory = $true)] [int] $index

[string]$range = [string]::Empty;
if ($Index -lt 0 ) { return $range; }

[int]$a = 26;
$mfr = [Math]::Floor([Math]::Log(($Index) * ($a – 1) / $a + 1, $a))
[int]$x = [int]$mfr;
$Index -= [int]([Math]::Pow($a, $x) – 1) * $a / ($a – 1);
for ([int] $i = $x + 1; $Index + $i -gt 0; $i–) {
$range = ([char](65 + $Index % $a)).ToString() + $range;
$Index /= $a;
return $range;


So in another method that loops through the columns I could do the following


foreach ($tmpDataColm in $table.Columns) {

# This is the index integer value of the column in the columns array of the datatable

$colIndex = $table.Columns.IndexOf($tmpDataColm);


$colLetter = ExcelColumnIndexToName($colIndex);

#$excelCol = ExcelColumnIndexToName($table.Columns.IndexOf($tmpDataColm));

Write-Host(“Table Column index value: $colIndex to Excel letter: ” + $colLetter );



So the output in the console looks like this


You can use this to make range selections without knowing how many columns the sql is creating and etc…


Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING

I needed to pull data from a ASE Database (T-SQL), filter it and then dump it into a excel workbook.  Finally wrap all that up and attach it to a Email via SMTP…

See below for inline code or jump over to my github repo

Continue reading “Powershell importing data from SQL into Excel COM OBJECT interop and EMAILING”

Quick NODE Demo site exposed to Internet…


Simplest option ever: local tunnel

Even if you have a dynamic IP, or you’re under a NAT, you can deploy your app and serve the requests right from your computer using a local tunnel.  This option is suited for some quick testing, demo a product or sharing of an app with a very small group of people.

A very nice tool for this, available on all platforms, is ngrok.
Using it, you can just type ngrok PORT and the PORT you want is exposed to the internet. You will get a domain, but with a paid subscription you can get a custom URL as well as more security options (remember that you are opening your machine to the public Internet).

Another service you can use is

‘ILoggerFactory’ does not contain a definition for ‘AddLog4Net’ and no accessible extension method

To fix this issue you need to run the npm command to install the MS Extension.

If you are getting this error:

‘ILoggerFactory’ does not contain a definition for ‘AddLog4Net’ and no accessible extension method ‘AddLog4Net’ accepting a first argument of type ‘ILoggerFactory’ could be found (are you missing a using directive or an assembly reference?)

You will need to add this extension to our project: [ link ]

Run this NUGET Command from the Package Manager Console in Visual Studio.

install-package Microsoft.Extensions.Logging.Log4Net.AspNetCore


VUE.JS and Angular – Enable Windows Authentication In Web API And Angular

Original Article:

Re-posted in case site goes away

Other resource:




In this article, we will learn about how to use inbuilt Windows authentication in Web API and Angular application for authentication and authorization purposes.


We have a requirement for in-house project development in the Angular App using Web API. As the purpose of this application is to use inside office only,  so it’s suggested to use Windows Authentication mode.


To access any web API from Angular or any Ajax method Web API must be CORS (Cross Origin Resource Sharing) enabled otherwise the request is not executed.

You can achieve this by referring to the below links.

Step 1

Create Web API Project and in Web.config select Authentication mode as “Windows”,

Enable Windows Authentication in Web API and Angular App


Web Config Code snippet

  1. <system.web>
  2.     <authentication mode=“Windows” ></authentication>
  3. </system.web>

Or you can publish web API project in IIS and Enable Windows Authentication from there.

Enable Windows Authentication in Web API and Angular App


So, based on the above screenshot you can enable Windows authentication for Web API Project.

Step 2

Use Authorize attribute on the controller or on any action method for security.

Enable Windows Authentication in Web API and Angular App


Code snippet for WebAPI Controller

  1. [EnableCors(origins: “*”, headers: “*”, methods: “*”, SupportsCredentials = true)]
  2.     public partial class WebAPIController : ApiController
  3.     {
  4.         /// <summary>
  5.         /// This method contains Authorize attribute for authentication and authroization
  6.         /// </summary>
  7.         /// <returns></returns>
  8.         [HttpGet]
  9.         [Authorize]
  10.         [Route(“api/AuthenticateUser”)]
  11.         public HttpResponseMessage AuthenticateUser()
  12.         {
  13.             if (User != null)
  14.             {
  15.                  return Request.CreateResponse(HttpStatusCode.OK, new
  16.                 {
  17.                     status = (int)HttpStatusCode.OK,
  18.                     isAuthenticated = true,
  19.                     isLibraryAdmin = User.IsInRole(@“domain\AdminGroup”),
  20.                     username = User.Identity.Name.Substring(User.Identity.Name.LastIndexOf(@“\”) + 1)
  21.                 });
  22.             }
  23.             else
  24.             {
  25. //This code never execute as we have used Authorize attribute on action method
  26.                 return Request.CreateResponse(HttpStatusCode.OK, new
  27.                 {
  28.                     status = (int)HttpStatusCode.BadRequest,
  29.                     isAuthenticated = false,
  30.                     isLibraryAdmin = false,
  31.                     username = “”
  32.                 });
  33.             }
  34.          }
  35.     }

As per the above screenshot, I have added [Authorize] attribute in AuthenticateUser Action method. This attribute makes sure that action is only executed if the user entered a valid credential otherwise it will display 401 Unauthorized access.

Here, I have added [Authorize] attribute only to action method. This can be put at Controller level as well and if the application has multiple Roles then it can be extended by passing Role name along with Authorize Attribute. You can explore more from WebAPI Authorization

While testing WebAPI from localhost (With Visual Studio by hitting F5 or Ctrl + F5) I am always getting this error {“Message”:”Authorization has been denied for this request.“}. However, if I publish this same website in IIS then I am getting a valid response as below with no extra work required.


Here, username (admin it can be any user) is logged in user in windows system (or Virtual Machine).

Stack Overflow Question –

When we run or debug the application from Visual Studio it is not hosted in IIS, instead it hosts in IISExpress which is part of the Visual Studio and stores the minimum required configuration or default configuration to run any application.

So, based on the provided solution from above Stack overflow question I have enabled windows authentication (<windowsAuthentication enabled=”true”>) in “applicationhost.config” file which resides at Project root directory “.vs\config”, this folder is hidden you must enable the show all hidden files and folder option.

Enable Windows Authentication in Web API and Angular App


By Default, Windows authentication value is false in  “applicationhost.config”

Now, we have successfully enabled Windows authentication in WebAPI Project.

Step 3

As per the prerequisite enable CORS at controller level along with SupportCredentials true,

Enable Windows Authentication in Web API and Angular App


As per screenshot, enable CORS with the provided configuration. Here Instead of “*“(Allow from any origin), you can restrict with specific IP Address or domain name.

Step 4

CORS is enabled from the server side. Now while requesting API, pass flag withCredentials: true from the frontend.

For jQuery Ajax you must pass the request as below.

  1. $.ajax({url:apiURL ,xhrFields: {   withCredentials: true }, success:successHandler });


For Angular you must pass the request as below.

  1. private options = new RequestOptions({ withCredentials: true });
  2. this.http.get(this.baseUrl, this.options)

Code Snippet

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  5. $(document).ready(function(){
  6.     $(“button”).click(function(){
  7.     // IIS localhost but with host domain name, with support credential true in Enable CORS
  8.      //var apiURL=”;;
  9.     // IIS Localhost with support credential true in Enable CORS
  10.     //var apiURL=”http://localhost:3005/api/AuthenticateUser&#8221;;
  11.     //IIS Express with support credential true in Enable CORS
  12.     var apiURL=http://localhost:51647/api/AuthenticateUser&#8221;;
  13.         $.ajax({url:apiURL ,xhrFields: {
  14.                         withCredentials: true
  15.                     }, success: function(result){
  16.                     console.log(JSON.stringify(result));
  17.                     document.write(JSON.stringify(result));
  18.             $(“#div1”).html(JSON.stringify(result));
  19.         }});
  20.     });
  21. });
  22. </head>
  23. <body>
  24. “div1”>

    Let jQuery AJAX to Access Web API Response with Windows Authentication

  25. <button> Click Me to Get Web API Response</button>
  26. </body>
  27. </html>

Now, we have successfully consumed Windows Enabled Authenticated WebAPI .

In case of accessing WebAPI, the dialog for login and password prompts infinitely for the correct password. Then you need to disable the loopback check by executing PowerShell command

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name “DisableLoopbackCheck” -value “1” -PropertyType dword

Reference link


In this article, we learned about how to enable Windows Authentication in Web API and Consume Secure web API from jQuery Ajax and Angular App.