September 17, 2003 at 9:35 am
Hi,
Is there someway to convert julianday in SQL?
I have all my tables in the server that include JulianDays, could I convert these into normal date format as MMDDYYYY writing some T-SQL probably?
Thanks A Lot!!
September 17, 2003 at 10:21 am
Something like this might work for you:
declare @jdate char(7)
set @jdate = '2003226'
select convert(char(10),
dateadd(day,cast(substring(@jdate,5,7) as int)-1,cast(substring(@jdate,1,4) + '-01-01' as datetime))
, 101)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 1:05 pm
The values are just the number of the day of an year. I have got year in one column, and just the julian day ( as say, 245) in the next column.
I did try the code, changing the length of the character to 3, but it might vary too, because the julian day values range from 1 - 365/366.
Any more help...
Thanks A Lot!
September 17, 2003 at 1:32 pm
If they are only the day of the year, how do you know which year they are for?
For example...Julian date 060 is March 1st ONLY three years out of four. Each leap year 060 is February 29th.
Using CHAR(3) won't matter regardless if your julian date is 1, 11, or 111. It will just use spaces to fill out the column to three characters.
-SQLBill
September 17, 2003 at 1:42 pm
I have a column separately for years. Julian days are in the next column.
Could you please suggest some real good books or online sites to learn in more detail about the SQL queries?
Thanks so much..
September 17, 2003 at 1:45 pm
Maybe these examples will help. There is a 1, 11, 245,365, 366 for Julian Day.
declare @jday varchar(3)
declare @jyear varchar(4)
set @jday = '245'
set @jyear = '2000'
select replace(convert(char(10),
dateadd(day,cast(@jday as int)-1,cast(@jyear + '-01-01' as datetime))
, 101),'/','')
set @jday = '366'
set @jyear = '2000'
select replace(convert(char(10),
dateadd(day,cast(@jday as int)-1,cast(@jyear + '-01-01' as datetime))
, 101),'/','')
set @jday = '1'
set @jyear = '2000'
select replace(convert(char(10),
dateadd(day,cast(@jday as int)-1,cast(@jyear + '-01-01' as datetime))
, 101),'/','')
set @jday = '11'
set @jyear = '2000'
select replace(convert(char(10),
dateadd(day,cast(@jday as int)-1,cast(@jyear + '-01-01' as datetime))
, 101),'/','')
set @jday = '365'
set @jyear = '2001'
select replace(convert(char(10),
dateadd(day,cast(@jday as int)-1,cast(@jyear + '-01-01' as datetime))
, 101),'/','')
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 2:23 pm
I could do the conversion with each julian day specified. Is there a means such that we could select the julian days column (for individual year) and run a query on that.
Becuase the tables that I have got are real big ones, could I do something which takes in good bulk of julian days at once and run the query on it for conversion?
Thanks A Lot for all the help !
September 17, 2003 at 2:39 pm
Sure! I think the where clause is what you are looking for:
select replace(convert(char(10),
dateadd(day,cast(jday as int)-1,cast(jyear + '-01-01' as datetime))
, 101),'/','') as MMDDYYYY
from mytable
where jyear = '2003'
Now if you are trying to update MMDDYYYY column then do
update mytable
set MMDDYYYY = replace(convert(char(10),
dateadd(day,cast(jday as int)-1,cast(jyear + '-01-01' as datetime))
, 101),'/','')
where jyear = '2003'
or something like one of these.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 3:14 pm
I get this error :
Error converting data type varchar to float.
Could I possibly know the best place to learn writing these kind of codes.
Thanks a lot..
September 17, 2003 at 3:25 pm
What does your code look like As far as learning I would suggest you find a good book with lots of T-SQL examples. Possibly:
http://www.sqlserverbyexample.com/
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 3:31 pm
SELECT REPLACE(CONVERT(char(10), DATEADD(day, CAST(JULIAN_DAY AS int) - 1, CAST([YEAR] + '-01-01' AS datetime)), 101), '/', '') AS MMDDYYYY
FROM ISCFINAL2_POINT
WHERE ([YEAR] = '1964')
I just run this in QueryAnalyser, it gives out error as :
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
September 17, 2003 at 3:41 pm
Not sure about that error.
What are the data types and lengths for JULIAN_DAY and YEAR?
Are you sure all your data in these columns contains integers?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 4:03 pm
They are of float type. The lengths are declared as 8.
September 17, 2003 at 4:34 pm
Thats what I thought, so try this:
SELECT REPLACE(CONVERT(char(10), DATEADD(day, CAST(JULIAN_DAY AS int) - 1, CAST(cast([YEAR] as char) + '-01-01' AS datetime)), 101), '/', '') AS MMDDYYYY
FROM ISCFINAL2_POINT
WHERE ([YEAR] = '1964')
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 18, 2003 at 3:50 am
I've had great results with both of the following user defined functions:
1) The function that a Mike G wrote which is located at http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=341&lngWId=5 .
2) The following function that I created by modifying the code in Victor Vogelpool's "CodeProject" article http://www.codeproject.com/datetime/exceldmy.asp?target=excel%7Cserial%7Cdate
CREATE FUNCTION dbo.fnToSerialDate (@date datetime)
-- fnToSerialDate
--
-- Date:20030917
--
-- Usage:
-- Converts a datatime value to a serial date number
-- This is the same thing as entering a date value such as 7/31/1972 into Microsoft Excel
-- and then formatting the cell as a number to get 26511 instead of the date format
--
-- Syntax: fnToSerialDate('datetime expression')
--
-- Arguments:
-- 'datetime expression' = Date and time data from January 1, 1753 through December 31, 9999,
-- to an accuracy of one three-hundredth of a second
-- (equivalent to 3.33 milliseconds or 0.00333 seconds).
-- Values are rounded to increments of .000, .003, or .007 seconds.
--
-- Errors:
-- If 'datetime expression' is not a valid date or date time combination the following error is
-- returned to the user:
--
-- Server: Msg 242, Level 16, State 3, Line 1
-- The conversion of a char data type to a datetime data type
-- resulted in an out-of-range datetime value.
--
-- Author:
-- BAD (Brent Alan Dorsey) - MCSE
-- Dell Computer Corporation
--
--Thanks and credit go to: Victor Vogelpoel
-- a thirty-something software engineer, consulting in The Netherlands.
--http://www.codeproject.com/datetime/exceldmy.asp?target=excel%7Cserial%7Cdate
--
--Rev:1.0
RETURNS INT
AS
BEGIN
DECLARE @month smallint,
@day smallint,
@year smallint,
@serialdate int
SELECT @month = MONTH( @date )
SELECT @day = DAY( @date )
SELECT @year = YEAR( @date )
-- Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a leap year, but Excel/Lotus 123 think it is...
IF ( @day = 29 and @month = 2 and @year = 1900 )
RETURN 60
ELSE
BEGIN
-- DMY to Modified Julian calculate with an extra substraction of 2415019.
SELECT @serialdate = ( ( 1461 * ( @year + 4800 + ( ( @month - 14 ) / 12) ) ) / 4) +
( ( 367 * ( @month - 2 - 12 * ( ( @month - 14 ) / 12 ) ) ) / 12) -
( ( 3 * ( ( ( @year + 4900 + ( ( @month - 14 ) / 12) ) / 100) ) ) / 4) +
@day - 2415019 - 32075
END
IF ( @serialdate < 61 )
SELECT @serialdate = ( @serialdate - 1 )
RETURN @serialdate
END
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply