Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Can someone explain the date format in the Report Writer?

I am trying to figure out the numbering system used by the Report Writer SQL. For instance when I look at the Availability - This month it shows

DateTime BETWEEN 38655 AND 38664

What do the numbers 38655 AND 38664 represent? It doesn't look like Epoch time and I can't (or haven't found a reference in the manual) find out what it relates to.

I am trying to find an equivalent to '1/1/1900'

Thanks

Find more posts tagged with

Accepted answers

All comments

darryld

I thought I could help but now I'm no sure

This from MS

"Date and time can be stored in SQL Server in datetime or smalldatetime. The datetime data type can store dates from January 1, 1753 to December 31, 9999 to an accuracy of up to 0.003 part of a second. The smalldatetime data type can store data from January 1, 1900 to June 6, 2079 with accuracy of up to the minute.

SQL Server takes into account a system reference date, which is called the base date for SQL Server. This base date is January 1st, 1900. It is from here that the main problem stems. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight."

But

When I did th maths your day numbers fall in Nov/Dec 2005 and when I look at a similar report on my server the numbers are different and smaller

Perhaps someone else can help us both

sedmo

The numbers represent the number of days since 1/1/1900. You can experiment with this by executing the SQL listed below.

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = 38655

SET @EndDate = 38664

select @StartDate as Start, @EndDate as Finish

This should return:

Start Finish

------------------------------------------------------------------------------

2005-11-01 00:00:00.000 2005-11-10 00:00:00.000