convert datetime to excel format

could  try these

The following example uses different date formats to handle date strings in different formats.

-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar DATETIME;
SET @datevar = '12/31/1998';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000

-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/31/12';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000

-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/12/31';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000

Taken from http://msdn2.microsoft.com/en-us/library/ms189491.aspx

Excel date format is just a long number, its not formatted like yyyy mm dd hh:ss...it would look something like 289034578902354. I'm unsure how to convert it and all the googling I've done only shows how to convert from excel to datetime.

> what value is currently stored in Excel?

None. The spreadsheet is being generated using values from the database.

perhaps this helps you explaining how the datetime format is stored.
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

In excel Click format, cells, number tab, click date and select the type you want.

might have to selct custom option and more dates are there

From Excel Help:  January 1, 1900 = 1
Each day after increments by 1, with hour, min, sec represented as portions of a day.

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for the Macintosh uses a different date system as its default.
Most functions automatically convert date values to serial numbers.

jstretch, I don't understand why you would need to "convert sql server 8 datetime value to excel format", since you say "The spreadsheet is being generated using values from the database.".  This would automatically store it in the SERIAL number format and DISPLAY using your format cells NUMBER FORMAT, i.e. "mm/d/yyyy"

quote: "This would automatically store it in the SERIAL number format"

This is a false assumption. WHAT would automatically store it in the SERIAL format? I'm not starting with an EXCEL sequential serial number, I'm starting with a datetime as a STRING and putting that STRING into an excel cell. EXCEL doesn't automagically convert my string (eg: 01/01/2007 08:00 or whatever output format) to an excel sequential serial number.

So I need a query to give me the number of days from Jan 1, 1900 to the date in question. Then I output that number to excel and format cells. What about time? I'm assuming its a decimal past that serial number? eg: 1234123412.12342

When I do an insert into a Excel Sheet using the code in the Code Snippet, this turns up in Excel:
Nov 26 2007 10:30AM | EE | Yveau
(| indicating the next cell)
Format cells tells me 'General' ... so it seems to work by default.

Hope this helps ...

-- inser into named area
declare @date datetime
set     @date = '20071126 10:30'
 
insert
into    OPENDATASOURCE
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="X:\Path\To\Yveau.xls";Extended Properties=Excel 8.0')...[Yveau]
values  (@date,'EE','Yveau')
 
SELECT  *
FROM    OPENDATASOURCE
(       'Microsoft.Jet.OLEDB.4.0'
,       'Data Source="X:\Path\To\Yveau.xls";Extended Properties=Excel 8.0')...[Yveau]
 
-->> Result:
-------------------- ---- -------
Nov 26 2007 10:30AM  EE   Yveau
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Yveau,

Thanks, but the database wont have access to the filesystem where the XLS is residing. So I cannot use that method. I'm geting a result set back to a script and the script is generating the XLS file through an API. I just need a date converted into an XLS serial date number.