Question

我正在使用PHP查询服务器2008R2的一些数据,并收到以下错误:

PHP Warning: mssql_query(): message: Conversion failed when converting date and/or time from character string. (severity 16) in /var/www/html/BSC_Entry.php on line 14
PHP Warning: mssql_query(): General SQL Server error: Check messages from the SQL Server (severity 16) in /var/www/html/BSC_Entry.php on line 14

Here s the code block:

  3 <?php // Initialise all database actions
  4     //IP of server
  5     $server =  x.x.x.x ;
  6 
  7     // Connection to MSSQL - possibly use password file
  8     $link = mssql_connect($server,  user ,  pass );
  9     if (!$link) {
 10         die( Something went wrong while connecting to MSSQL );
 11     }
 12 
 13     // Declare query to return BSC_Name, BSC_Owner and  
 14     $qBSCInfo = mssql_query( SELECT * FROM dbo.BSC_Info; , $link);
 15 
 16 ?>

起初,我通过下文“Q”作为直径,以至mssql_query,但在收到错误后,在Mmsql_query过于复杂的情况下,在BSC_Info(经适当许可)之上建立了数据库观点:

SELECT DISTINCT 
    dbo.BSC.BSC_ID,
    dbo.BSC.BSC_Name, 
    dbo.BSC.BSC_Owner, 
    DATEDIFF(M, MAX(CONVERT(DATETIME, LEFT(dbo.BSCDataSet.DatePeriod, 4) 
                      + RIGHT(dbo.BSCDataSet.DatePeriod, 2) +  01 )), CONVERT(DATETIME, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 4) + RIGHT(LEFT(CONVERT(VARCHAR, GETDATE(), 
                      120), 7), 2) +  01 )) AS Periods_to_Current
FROM dbo.BSC 
LEFT OUTER JOIN dbo.BSCDataSet 
ON dbo.BSC.BSC_ID = dbo.BSCDataSet.BSC_ID
GROUP BY dbo.BSC.BSC_ID, dbo.BSC.BSC_Name, dbo.BSC.BSC_Owner

为了澄清问题,Kall管理室的查询工作从一个桌子返回了一些领域,同时从现在到更早的日期(几个月)相差(储存在数据库中,作为VARCHAR-YYYYYYMM格式)。为了防止出现任何半月的问题,我确定与月份第一天的比较日期。我确信,这样做有更宽松的办法,但我几乎没有什么服务器经验,也没有这方面的购买力平价!

数据类型:

BSC_ID - numeric(5,0)
BSC_Name - varchar(50)
BSC_Owner - varchar(50)
Periods_to_Current - int

任何帮助都会受到高度赞赏。所有人!

Answer 1

因此,该法典有若干问题:

php.ini file needed the mssql setting: mssql.datetimeconvert = 0
Any CONVERT-ed date fields needed the date format 121
the original code tried to perform conversions, date arithmetic and MAX() functions on a potentially NULL date field - oops

我现在对法典进行了更适当的修改(并投入工作):

SELECT 
  dbo.BSC.BSC_ID,
  dbo.BSC.BSC_Name, 
  dbo.BSC.BSC_Owner, 
  CASE maxview.maxDate 
    WHEN NULL THEN NULL 
    ELSE DATEDIFF(M,CONVERT(DATETIME, LEFT(maxview.maxdate, 4)+ RIGHT(maxview.maxdate, 2) +  01 , 121),GETDATE()) 
  END 
FROM dbo.BSC 
LEFT OUTER JOIN (SELECT BSC_ID, MAX(dbo.BSCDataSet.DatePeriod) as maxDate 
FROM dbo.BSCDataSet GROUP BY BSC_ID) maxview 
ON dbo.BSC.BSC_ID = maxview.BSC_ID;

希望这将帮助人们摆脱!

友情链接