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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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!