How to set the base time for DateTime in IIS (ASP) for compatibility mode?

Question

We have updated our servers with Windows 2008 Server, but we are still using SQL Server 2005 in our production software. The problem comes in one part of the system where we store times as datetimes. Since ever, storing a time in a datetime column stored the base date as the date. It is 1900-01-01 in SQL Server 2005. Back then, IIS understand that this is the default case, so our ASP application printed only the time part of the datetime and our system worked as expected.

With this sa[i]d update, the base datetime seems to have changed to 1899-12-30, so our ASP pages display the full datetime, for example 1900-01-01 07:30:00, instead of only the time (07:30:00), as this is not the default date anymore.

I know this isn't the best approach for the system, it wasn't me who developed it in the first place, I'm just maintaining this, so I'd like to know if there is someplace in the IIS configurations menus or in Windows where I can set the base date for my applications. I've read somewhere else that SQL Server 2005 uses 1900-01-01 as basedate where other MS applications use 1899-30-12, but I don't think this is it, I hope I can change this.

I don't know yet the consequences of extracting the time and using it in this part of the system (altering our software) instead of messing around with configurations (input to our software), but I guess the blind spots would be everywhere.

====== Example, as asked:

I can't supply an exact example right now (I'm home for the weekend), but I can try:

set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SELECT * FROM TABLE WHERE ID=123", MyConnection, 1, 3
rs("MyDateTimeColumn") = Request.Form("MyTimeOnlyHtmlInput") 'Formatted as 07:14:50, for example
rs.Update 'Saved to the Data Base
rs.Close
rs.Open "SELECT * FROM TABLE WHERE ID=123", MyConnection, 1, 3 'Retrieving data back
echo "<input type='text' value='" & rs("MyDateTimeColumn") & "' />"

That code used to work, I'd feed "01:23:45" in the input, post it to this code and it would print the input with the time only. Right now, what I get in the input is "1899-12-30 01:23:45.000".

UPDATE: I've asked our Server Manager for help, he'll try MSFT as resource. I'll update this if I get news. Thank you all for the help. I'll update this if I get some news.

Answer 1

I don't understand the details of your implementation, but if you know the column is only meant to contain time info, why not just select the time portion like this:

select convert(varchar(10), getdate(), 108)

If you are storing times longer than a day, it gets a bit trickier. In this case you can do something like this to figure out what the base date is:

select case 
    when cast(0 as datetime) = '1900-01-01 00:00:00.000' then 0
    when cast(0 as datetime) = '1899-12-30 00:00:00.000' then 1
    else -1 --if you get this number back, throw an error
end

If the above query returns a 1, your app knows it needs to add a day to the retrieved value before determining the time portion.

Answer 2

You appear to be saying that this problem has only surfaced when you upgraded to Windows 2008. I'm finding that a little hard to grasp.

SQL servers "zero" date is 1900-01-01 and that hasn't changed
VBScripts "zero" date is 1899-12-30 and that hasn't changed either.

There is no system setting which affects either of these base dates.

I suspect that something else has changed in your system that has changed the way it behaves. It may be related to your server upgrade but its difficult to perceive what that would be. An example of the code that was working and now isn't would be good?