asked on

Query Date and Time in Different Columns

I have a table that has a modified Julean date stored as a varchar in one column and a time value stored as a varchar in another column. I would like to be able to query for all values that are between a start date/time and an end date/time. For example if my start date is 108152 and start time is 175945 and my end date is 108155 and end time is 085500 then I would like all of the values inbetween. Please provide me with an example where statement.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schlepuetz

ASKER

I agree with the bad idea thing but that is the nature of the beast when dealing with other people's stuff. Let me give your suggestions a whirl.

Mark Wills

Time seems to be straight time - 24 hour clock... So, try these... and convert either Julian to date or date to julian and compare time seperately....

-- JULIAN TO SQL SERVER
declare @date char(10)
set @date=108152
select dateadd(dd,@date-(@date/1000)*1000-1, convert(datetime,'01/01/'+ right(convert(varchar,@date/1000),2))) as Date_From_Julian

-- SQL SERVER DATE TO JULIAN
declare @date datetime
set @date=getdate()
select (100+year(@date)-2000)*1000+ datediff(d, convert(datetime,('01/01/'+convert(char(4),year(@date)))),@date)+1 as Date_To_Julian

-- TIME
declare @date datetime
set @date=getdate()
select replace(convert(varchar,@date,108),':','') --or 114

schlepuetz

ASKER

Here is final solution that I used.
cast([Date]as varchar(6)) + right('000000' + cast([Time]as varchar(6)),6)

I used the right to make sure that I had the same number for the times as some of ny times where not the full 6 characters.

Sorry mark wills I had tried before converting the date and time to normal date time values but the query was too slow. The difference was 15 seconds versus 2 seconds.

Thanks Qlemo for the push in the right direction!