Compare Time in SQL (without using date)

Sometimes you have the need to compare time without the date component. I have a table where I’m storing only TIME  (e.g., 10:30:00 or 13:35:45) without the date component in Microsoft SQL server. If you try to use greater than (>), smaller than (<) or equal to operator (=) to compare a DATETIME or TIME data type, the SQL server may throw following error:

The data types datetime and time are incompatible in the greater than or equal to operator.

In order to overcome this error you will have to use the following trick/hack to compare datetime or time data types:

1. First, CAST the TIME data type to DATETIME:
   CAST(myTime as datetime)
   - myTime is the name of my column; it’s SQL data type is TIME
   
2. CAST the above to FLOAT
   CAST(  CAST(myTime as datetime) as float)
   
3. Use the <, >, or = operator as:
   SELECT CAST(CAST(myTime as datetime) as float) as myTimeAsNumber
   FROM myTable
   WHERE CAST(CAST(myTime as datetime) as float) <= CAST(CAST(‘14:15:00’ as datetime) as float)
   - This will return all records on or before 02:45 PM. Usually Microsoft SQL server stores time in 24 hours format, i.e., 14:15:00.000 format.

After I CAST myTime as datetime and its result to float I got 0.75. That indicates 18 hours after 1/1/1900 (SQL Server’s base date), i.e., 75/100ths of a fractional day – it’s evening 6:00 PM. So when I’m casting my column as datetime and the result to float data type I end up with a number that is equal or smaller to 1.

Casting 24:00:00 time to datetime and the result to float will give 1, which indicates 24 hours (1 Day) after SQL server’s base date. And, casting 12:00:00 time to datetime and the result to float will give 0.5, which indicates 0.5 day or 12 hours.

Moreover, you may end up with following error if you try to cast time as float:

CAST(CAST(myTime as time) as float) will throw following error:

Explicit conversion from data type time to float is not allowed.

To fix this, cast data type time (myTime) as datetime and then cast the result as float as we have done above.

Make sure NOT to use FLOOR function as it will strip off decimal portion of number. Hope this post will be useful for people trying to compare datetime or time data types.

Run IIS, ASP.net and Apache Together Without Multiple IP Addresses

Many people want to run both IIS and Apache Web Server together. We have already blogged on similar lines: how to add or assign multiple IP addresses to your computers and how to configure their hosts files to loopback virtual hosts to development servers and how to run IIS and Apache together when your computer has multiple IP addresses. Additionally, we have written about configuring Apache to handle different hostnames.

This post will help you run IIS and Apache Web Server together even if you can't manage to add multiple IP addresses (may be because your ISP refuses to assign you so).

It is fairly simple to use Apache Web Server as a proxy to reach IIS. Your sites users will make direct requests to Apache Web Server and when we have finally configured our Apache Web Server, all requests will be proxyed to IIS. You can also configure multiple domains (blogged earlier) in to only proxy requests for particular virtual hosts (particular websites).

Here is what you need to do:

1. Configure your IIS website to listen to port number 8080 or any other number not in use in your system. So if your website URL was http://www.my-site.com, it will now be http://www.my-site.com:8080/
2. Start Apache Web Server and make sure Apache can listen to port 80. If Apache web server FAILS, you can use bind settings specified in the earlier post that includes info on changing bind settings of IIS. You may have to restart your server.
   
3. Make sure you can start both web server and hit both IIS and Apache from local machine using different ports.
   
4. Now open Apache configuration file, httpd.conf. This file is located inside the installation directory under /conf folder. Usually it is placed under: C:\Program Files\Apache Software Foundation\Apache2.2\conf
5. Scroll to the bottom of this file and add the following text to tell Apache Web Server to load the two following modules and enable proxy sites capability:
   LoadModule proxy_module modules/mod_proxy.so
   LoadModule proxy_http_module modules/mod_proxy_http.so
   
   ProxyRequests Off
   
   
   Order deny,allow
   Allow from all
   
   
   NameVirtualHost *:80
   
   
6. At the bottom, add a new virtual host in the following way: (read howto if you are not sure about vhosts)
   
   ServerName www.my-site.com
   ServerAlias *.my-site.com
   Alias / "C:\Inetpub\wwwroot"
   DefaultType text/html
   
   Options Indexes FollowSymLinks
   AllowOverride none
   Order allow,deny
   Allow from all
   
   ProxyPass / http://www.my-site.com:8080/
   
   
   
7. Restart Apache Web Server and bingo, you're done!
   
   

Now if you access www.my-site.com your requests will be proxied to IIS by Apache Web Server and the only Web server users will be in direct access of will be Apache. Apache and IIS connection will be hidden from your end-users.

ProxyPass is the magic statement and the power of mod_proxy_http and mod_proxy means that you can run any other Application Server or Web Server behind your Apache Web Server and enjoy the benefits of multiple web servers.


This effectively means that you can now run ASP.net with Apache Web Server!
Note: Don't use this technique to create fake Yahoo or GMail sites inside a office network!

Add or Assign Multiple IP addresses on Windows or Linux to run IIS and Apache Web Server or Other Servers together

If you want to run more than two servers, say IIS and Apache Web Server on your system then your administrator needs to add IP aliases to your system in order to achieve this functionality.

Here are some simple steps on some of the more popular operating system varieties.

---Windows 2003 Web/Standard:

Navigate this path:

Start Button > Settings > Control Panel > Network Connections > Local Area Connection > Properties > TCP/IP > Advanced > Add

At this point you will be presented with a dialog box that prompts you for an IP address and a subnet mask. The IP address you enter will be the next available IP address in your assigned range. The subnet mask depends on the size of your allocation.

Some popular subnet masks will be 255.255.255.248 (5 IP addresses) 255.255.255.240 (13 IP addresses) 255.255.255.224 (32 IP addresses)

---RedHat Enterprise Linux, CentOS, and Fedora Core

Create a file in /etc/sysconfig/network-scripts using your favorite text editor (nano, vi, pico) called ifcfg-eth0-range0.

The contents of ifcfg-eth0-range0 should be:

IPADDR_START="X"
IPADDR_END="Y"
CLONENUM_START="0"

Where X is the second IP address in your range, and Y is the last IP address in your range.

After saving this file, type "service network restart" to enable these changes.

Note: Incorrectly modifying your network settings can render your server unreachable to the Internet.

Using Separate Host or Domain Names for Website Development on Windows with IIS or Apache Using Hosts File

Whether you run IIS or Apache you can run local sites similar to 'localhost'.

When you type localhost in your browser's address bar and hit enter, the address always translates to the loopback IP address 127.0.0.1 in IPv4, or ::1 in IPv6.

You can easily create similar names which will always translate to loopback IP address 127.0.0.1 or point to any specific IP address of your choice.

Here is what you have to do:

1. Press Windows key + R, the Run... dialog box will appear. - If your keyboard lacks the Windows key, you may click Windows Start button and then click on Run... option in the Start menu.

2. Type drivers and hit enter, the Windows explorer will appear.

3. Now browse to drivers/etc/

4. Open the host file with Notepad application.

You'll see at least on entry inside your host file. Almost all host file contain the following entry:

127.0.0.1 localhost

To add another entry, change your host file in this way:
127.0.0.1 localhost
127.0.0.1 local.com

Save your hosts file.

Try loading local.com from your browser, do NOT add www. You will see that you are hiting Apache or IIS on your own computer.

Now, reopen host file and add one more entry:

127.0.0.1 localhost
127.0.0.1 local.com
127.0.0.1 www.local.com

Save the file and try to load the new URL www.local.com from your browser and you continue to hit your local web server.

Another shot:

127.0.0.1 localhost
127.0.0.1 local.com
127.0.0.1 www.local.com
127.0.0.1 www.yahoo.com

Save the file and now you can NEVER access the real Yahoo but will see the localhost site from your web server.

Another important aspect:

127.0.0.1 localhost
127.0.0.1 local.com
127.0.0.1 www.local.com
127.0.0.1 www.yahoo.com
192.168.0.5 www.ebay.com

Save your hosts file. Now whenever you will type www.ebay.com on your browser you will be taken to the webserver running on your network computer that has the given IP address of 192.168.0.5.

Bingo, you're ready for the next tutorial on configuring Apache and IIS to serve or show different content when different domain names are passed.

Don't jump into conclusions and remeber that: 1. This will not change the settings of your websites users or anything outside your computer. 2. Just because you added ebay.com to your host file doesn't make you owner of that domain name. 3. Nobody else can visit your site by typing local.com and until they specifically update their hosts file 4. Don't try to hack into someone else's computer using this concept since you will be caught just too easily and it will be foolish to spend dozens of years in jail for such a simple hack. If you want to serve jail time then it better be some serious hack.

Running Multiple Websites or Domains with Different Domain Names or Host Names with Apache Web Server

If you have read the post on adding hosts names for development on IIS or Apache you may have already started wondering how can you serve or show different content using IIS or Apache.

 

 

Here we will show you just how easy it is to configure IIS or Apache web server to serve different sites when different hostnames are used.

 

You may be running any application server, like ColdFusion, PHP, Ruby on Rails, ASP.net, Tomcat, you will find the following information handy.

 

This article will help you configure your Apache web server to run and handle multiple domain names.

How to configure Apache Web Server to run multiple websites or domains or sub-domains:

1. Open httpd.conf file. This file is located inside the installation directory under /conf folder. Usually it is placed under: C:\Program Files\Apache Software Foundation\Apache2.2\conf
2. Scroll to the bottom of the text file and add the following text:
   NameVirtualHost *:80
   
3. For each new website we wish to add we add a vhost. You can copy and add the following lines:
   
   <VirtualHost *:80>
       ServerName sub.domain.com
       DocumentRoot "C:/Apache2.2/htdocs/test/"
   </VirtualHost>
   
4. Restart Apache and load sub.domain.com using the browser, if you’re lucky you will be able to access the pages inside the DocumentRoot specified in the previous step. You can also get an Access Denied or Directory listing denied error which will be fixed in the next step.
5. Add this (only when you’re getting errors mentioned in Step 4) to make sure users can access the pages:
   <VirtualHost *:80>
       ServerName test.codecurry.com
       DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/test/"
   <Directory "C:/Apache2.2/htdocs/test">
       Options FollowSymLinks
       AllowOverride all
       Order deny,allow
       Deny from all
   </Directory>
   </VirtualHost>
   
6. You can do much more than this. You can specify other names for this new domain:
   <VirtualHost *:80>
       ServerName test.codecurry.com
       ServerAlias www.sub.domain.com
       DocumentRoot "C:/Apache2.2/htdocs/test/"
   </VirtualHost>
   
7. No just that, you can add more than one name separating each with a single space:
   <VirtualHost *:80>
       ServerName test.codecurry.com
       ServerAlias www.sub.domain.com sub2.domain.com sub.another-domain.com
       DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/test/"
   </VirtualHost>
8. Now the final stroke adding one more site pointing to a separate domain:
   <VirtualHost *:80>
       ServerName sub.domain.com
       ServerAlias www.sub.domain.com
       DocumentRoot "C:/Apache2.2/htdocs/sub/"
   </VirtualHost>
   <VirtualHost *:80>
       ServerName localhost
       ServerAlias www.localhost.com localhost.com local local.com
       DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/"
   </VirtualHost>
   <VirtualHost *:80>
       ServerName dev.domain.com
       ServerAlias www.dev.domain.com
       DocumentRoot "D:/wwwroot/Inetpub/dev/"
   </VirtualHost>

 

Explanations:

NameVirtualHost *:80	Tells Apache Web Server that we want to listen to all requests made to any IP address bind to Apache. A simple IP-based virtual host will use the IP address of the connection to determine the correct virtual host to serve. Therefore you need to have a separate IP address for each host. With name-based virtual hosting, the server relies on the client to report the hostname as part of the HTTP headers. Using this technique, many different hosts can share the same IP address.
<VirtualHost *:80>	A new web site or virtual host defined.
ServerName sub.domain.com	The hostname for this virtual host.
ServerAlias www.sub.domain.com	Another hostname for this virtual host. More can be added by separating hostnames with a spaces.

 

Other cool examples:

Listen 8080 <VirtualHost 111.22.33.44:8080> DocumentRoot /www/domain2 </VirtualHost>	Makes Apache listen to port 8080 as well. Create a virtual host with specific IP address and specific port as well.
<VirtualHost 111.22.33.44:*> DocumentRoot /www/domain2 </VirtualHost>	Listens to all ports on the specified IP address.
<VirtualHost *:8080> DocumentRoot /www/domain2 </VirtualHost>	Listens to specific port number on all IP addresses of the server.
<VirtualHost _default_:*> DocumentRoot /www/default </VirtualHost>	Catching every request to any unspecified IP address and port, i.e., an address/port combination that is not used for any other virtual host.
<VirtualHost _default_:80> DocumentRoot /www/default80  </VirtualHost> <VirtualHost _default_:*> DocumentRoot /www/default  </VirtualHost>	Same as the previous one, But the server listens on several ports and We want to use a second _default_ vhost for port 80

Note: Some old browsers lacks the ability to send Hostname header in the requests and so websites that depend solely on ServerName or ServerAlias won’t work. A work around is to point such browsers to a directory using rewrite rules. Doing this will make your website available to all kinds of browsers:

<VirtualHost 111.22.33.44>
# primary vhost
DocumentRoot /www/subdomain
RewriteEngine On
RewriteRule ^/.* /www/subdomain/index.html 
</VirtualHost>

<VirtualHost 111.22.33.44>
DocumentRoot /www/subdomain/sub1
ServerName www.sub1.domain.tld
ServerPath /sub1/
RewriteEngine On
RewriteRule ^(/sub1/.*) /www/subdomain$1 
</VirtualHost>

<VirtualHost 111.22.33.44>
DocumentRoot /www/subdomain/sub2
ServerName www.sub2.domain.tld
ServerPath /sub2/
RewriteEngine On
RewriteRule ^(/sub2/.*) /www/subdomain$1 
</VirtualHost>

Good Luck.

Installing and Running IIS and Apache Server Together on Windows Vista or Windows XP or Windows Server 2003

[Edit] Added new posts: Run IIS behind Apache. and adding more than one IP address to your computer

I run Vista and I have been using Apache for last one month. I wanted to run Python on Apache using FastCGI module mod_fastcgi but a compiled Windows version of this module was not available from FastCGI web site. So, I decided to IIS7’s built-in FastCGI support.

“Why I chose FastCGI over standard CGI or mod_python?” is a question I would answer in a future post.

If you want to run both IIS7 and Apache web server together, than here’s the information you need, just the solution for you. By default IIS7 would listen to all IP addresses your machine is assigned and it means that you can install IIS on your system but you can not run IIS web sites until you turn off Apache HTTP server. In a situation like this IIS will throw this error when trying to start a website.

Cannot Start Web Site
---------------------------
There was an error while performing this operation.

Details:

The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020)
---------------------------
OK

The solution is to bind a specific and separate IP addresses to IIS and to Apache. There are mainly three steps involved in this.

First and foremost, make sure that you have two IP addresses assigned to your PC

First make sure you have more than one IP address assigned to your system by the cable modem you are using. In some cases you might be using a network router instead of cable modem. Whatever the case may be, you will definitely need a minimum of two IP addresses.

Update: If you can't get two IP addresses for your PC, you can use different port numbers for Apache and IIS.

Open a command prompt and type ipconfig and hit enter. Your system must show at least two IP addresses assigned to it. If you don’t have two IP addresses, get another IP address assigned to you. (Update: you may use two different port numbers)

Second, configure Apache to listen to a specific IP address

Open httpd.conf file from Apache/conf directory and edit the line with ServerName key. This is my new httpd.conf file:

#
# ServerName gives the name and port that the server uses to identify itself.
# This can often be determined automatically, but we recommend you specify
# it explicitly to prevent problems during startup.
#
# If this is not set to valid DNS name for your host, server-generated
# redirections will not work. See also the UseCanonicalName directive.
#
# If your host doesn't have a registered DNS name, enter its IP address here.
# You will have to access it by its address anyway, and this will make
# redirections work in a sensible way.
#
# Old value: ServerName 127.0.0.1:80
#
ServerName 192.168.1.177:80

Now, restart your Apache web server.

Finally, configure IIS to bind it to another IP address

IIS binding on Vista requires you to update IIS settings and you will need to use command line tool called netsh. Open a command prompt and type netsh.

netsh is only available on Windows Vista and you will need to start command line with elevated settings. Replace my IP address with your system’s IP address. Repeat “add iplisten ###.###.###.###” for all the IP address bind to IIS.

C:\Windows\system32>netsh
netsh>http
netsh http>add iplisten 192.168.1.164
IP address successfully added
netsh http>show iplisten
IP addresses present in the IP listen list:
192.168.1.164

For Other versions of Windows, binding an IP address to IIS:

If you are using other versions of Windows, you will need HttpCfg.exe utility. It is available by default on Windows Server 2003 but if you're using Windows XP you can install HttpCfg.exe as part of the Windows XP Service Pack 2 Support tools. Then open a command prompt and type the following, replacing xxx.xxx.xxx.xxx with the IP address you'd like to bind to IIS:

C:\Windows\system32>httpcfg set iplisten -i xxx.xxx.xxx.xxx

I hope this will help some of you with this specific requirement.

Flex 4 (Gumbo) Error: Type was not found or was not a compile-time constant: Matrix3D.

I was creating a prototype application using Adobe Flex SDK version 4 (codename Gumbo), and if like me you decided to play with Gumbo in Flex Builder, it may give you numerous compile time errors with no location. Similar to the one mentioned below:

Severity and Description: 1046: Type was not found or was not a compile-time constant: Matrix3D.

Location: Unknown

Here’s a easy fix for the solution:

1. Open Project Properties
2. Go to Flex Compiler tab.
3. Change the value of Require Flash Player version to 10.0.0 or above (I set it to 10.0.12) as mentioned in the snapshot below:

It will throw error even if you uncheck Require Flash Player, so make sure proper value is set.