[Resolved] Openquery - error converting timestamp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Openquery - error converting timestamp

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-29 : 12:07:47

Have following query:


create table	#PlantTable
	         		(	
                                   plant_id char(10) default ' ',
                                   plant_name char(20) default Null,
			           shift_date datetime,
			           outside_qty decimal(15,2) default Null,
			           intercompany_qty decimal(15,2) default Null 
                		)
Insert Into #PlantTable(shift_date, intercompany_qty, outside_qty)
SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT MAX(SHIFT_STARTED) as shift_date,
                                          SUM(CASE WHEN customer_description_1 = ''Ranger Construction Industries''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_description_1 <> ''Ranger Construction Industries''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE (void is null or void = false) and
                                           incoming_material = false and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started is not Null and
                                                                                       SD.shift_started = STK.shift_started))')

This happens only if no records is found in the SHFTDATE table (which could be the case) and I get error in the "shift_started" field (timestamp)

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

SALESTKT table:

2/23/2010 4:08:00 PM

SHFTDATE table ( 2 records)

ASTEC      5/25/2007 11:25:00 AM
R          (Null value)

I tried to change my query like this:

MAX(CASE WHEN SHIFT_STARTED IS NOT NULL
                    then shift_started
                      ELSE Null
                    end) as shift_date,

Did not help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 02:20:21

is it proper NULL or string 'null'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-30 : 07:48:14

I'm not sure, when I query the table the SHFTDATE table, the "shift_date" column is empty (does not show "Null" value).

So when there are no records selected (having a match) it complaints about the "max(shift_date.." If I leave out the "shift_date" the query works fine like this:

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT SUM(CASE WHEN customer = ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer <> ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE ((void is null or void = false) or
                                           incoming_material = false) and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started is not Null and
                                                                                       SD.shift_started = STK.shift_started))')

Result (which is correct):

Intercompany_qty   outside_qty
0	               0

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 01:04:01

whats the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-05-03 : 08:17:34

Tried:

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT    MAX(CASE WHEN shift_started IS NOT NULL
                                                        THEN shift_started
                                                   ELSE NULL
                                             END) AS shift_date,                                            
                                          SUM(CASE WHEN customer = ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer <> ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE ((void is null or void = false) or
                                           incoming_material = false) and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started is not Null and
                                                                                       SD.shift_started = STK.shift_started))')

Error (same as before)

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 10:09:00

try casting the field to date datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-05-03 : 12:17:53

Got it working this way:

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT CONVERT(MAX(shift_started), SQL_VARCHAR) as shift_date,                                          SUM(CASE WHEN customer = ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer <> ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE ((void is null or void = false) or
                                           incoming_material = false) and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started is not Null and
                                                                                       SD.shift_started = STK.shift_started))')

Thank you all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources