无法从 Julian INT 日期转换为常规 TSQL 日期时间
帮助我 Stackoverflow,我已经快要在键盘上敲出“HULK SMASH”来解决这个问题了。我仔细研究过,但显然我没有得到正确的东西。
尽管我使用的是 SQL 2005,但我正在使用从专有工具(Platinum SQL?)引用的 Julian 日期。当我运行 select 语句时,我可以将其“特殊”版本的 Julian 转换为日期时间。不幸的是,它不会插入到日期时间列中,当我尝试时,我收到以下错误:
将字符数据类型转换为日期时间数据类型导致日期时间值超出范围。
因此,我无法设置日期时间标准来运行存储过程的报告。
原值:733416 等效日历值:01-09-2009
下面是我的代码...我已经很接近了,但我不太明白出了什么问题,我需要我的转换语句将 Julian 值(733416)实际转换为兼容的 TSQL DATETIME价值。
SELECT
org_id,
CASE WHEN date_applied = 0 THEN '00-00-00'
ELSE convert(char(50),dateadd(day,date_applied-729960,convert(datetime, '07-25-99')),101)
END AS date_applied,
CASE WHEN date_posted = 0 THEN '00-00-00'
ELSE convert(char(50),dateadd(day,date_posted-729960,convert(datetime, '07-25-99')),101)
END AS date_posted
from general_vw
Help me Stackoverflow, I'm close to going all "HULK SMASH" on my keyboard over this issue. I have researched carefully but I'm obviously not getting something right.
I am working with a Julian dates referenced from a proprietary tool (Platinum SQL?), though I'm working in SQL 2005. I can convert their "special" version of Julian into datetime when I run a select statement. Unfortunately it will not insert into a datetime column, I get the following error when I try:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
So I can't setup datetime criteria for running a report off of the Stored Procedure.
Original Value: 733416
Equivalent Calendar Value: 01-09-2009
Below is my code... I'm so close but I can't quite see what's wrong, I need my convert statement to actually convert the Julian value (733416) into a compatible TSQL DATETIME value.
SELECT
org_id,
CASE WHEN date_applied = 0 THEN '00-00-00'
ELSE convert(char(50),dateadd(day,date_applied-729960,convert(datetime, '07-25-99')),101)
END AS date_applied,
CASE WHEN date_posted = 0 THEN '00-00-00'
ELSE convert(char(50),dateadd(day,date_posted-729960,convert(datetime, '07-25-99')),101)
END AS date_posted
from general_vw
您正在转换为 char 但需要一个日期时间,因此这是一个简单的解决方法。
您还使用“00-00-00”作为最小日期,但最小 TSQL 日期是“1753-01-01”。或者,您可以使用类似 ('1900-01-01') 的内容,但这需要更改“小于”date_applied 比较器。
我也添加了一个“小于”date_applied 比较器。我将其计算为“SELECT 729960 + datediff(day,convert(datetime,'07-25-99'),convert(datetime,'1753-01-01'))”。任何小于此值的数字都会导致日期下溢。
You're casting to char but want a datetime so that's one easy fix.
You were also using '00-00-00' as your minimum date, but the minimum TSQL date is '1753-01-01'. Alternatively you could use something like ('1900-01-01') but that would need a change to the "less than" date_applied comparer.
I've added a "less than" date_applied comparer too. I calculated this as "SELECT 729960 + datediff(day,convert(datetime, '07-25-99'), convert(datetime,'1753-01-01'))". Any number less than this would cause a date underflow.