Here are two pieces of code that may help you:
The first is the reverse of what you want to do - so it may help - it converts 01/01/04 to 20040101
The second converts a julian date from our ceridian payroll system which uses 12/28/1800 as the basis for it's julian dates.
CREATE procedure dbo.sp_convert_date_to_int
@parm_date datetime, @parm_date_int integer OUTPUT
/************************************************************************************/
/* Procedure: Convert Date To INt
* Date: 12/11/01
* Author: JMM
* Description:
* This procedure converts a date to an integer format of
* YYYYMMDD for faster lookups and sorting options.
* PARAMETERS:
* parm_date - date to be converted
************************************************************************************/
as
declare @w_month as varchar(3)
declare @w_temp as varchar(10)
declare @w_day as varchar(3)
declare @w_year as int
declare @w_date_str as varchar(15)
declare @w_date_int as int
begin
/*
* Convert the date section by section
*/
SELECT @w_temp = MONTH(@parm_date)
if LEN(@w_temp) < 2
begin
select @w_temp = '0' + @w_temp
end
select @w_month = @w_temp
SELECT @w_temp = DAY(@parm_date)
if LEN(@w_temp) < 2
begin
select @w_temp = '0' + @w_temp
end
select @w_day = @w_temp
SELECT @w_year = DATEPART(yyyy,@parm_date)
SELECT @w_date_str = str(CAST(@w_year as CHAR)) + @w_month + @w_day
SELECT @parm_date_int = convert(int, @w_date_str)
-- print 'date = ' + str(@parm_date_int)
end
GO
>>>>>>>>>>>>>Convert Julian Date<<<<<<<<<<<<<<<<<<<<<
CREATE FUNCTION JulDate (@Date int)
RETURNS datetime AS
BEGIN
RETURN(DATEADD(DAY,@Date,'12/28/1800'))
END
