Преобразование даты из Nvarchar в smalldatetime

Lalaland спросил: 03 ноября 2018 в 08:47 в: sql-server

Я пытаюсь преобразовать столбец с датой, который имеет следующий формат: 21 декабря 2017 г., 06:42:57, чтобы показать 2017 г. / 21 декабря, 06:42 но продолжайте получать:

Преобразование типа данных nvarchar в тип данных smalldatetime приводит к значению вне допустимого диапазона.

Вот код, который я использую:

DECLARE @FI NVARCHAR(10) =''
,@SLA_MONTH INT = 9 IF OBJECT_ID('tempdb..#SLA_ALL') IS NOT NULL DROP TABLE #SLA_ALL
SELECT [Main FI],Batchno,[Legal Entity Name],I.[Goldtier ID]
, I.[Date and Time notification recieved from FI to upload entities to TR],MAX(CAST(SUBSTRING(P.[Create Timestamp],8,4)+'/'
+SUBSTRING(P.[Create Timestamp],1,3)+'/'
+SUBSTRING(P.[Create Timestamp],5,2)+' '
+SUBSTRING(P.[Create Timestamp],13,5) AS smalldatetime))[Keys Generated]
,MIN(CONVERT(DATETIME,C.Timestamp)) 'Consent recevied'
,MIN(CONVERT(DATETIME,S.Timestamp)) 'Documents Requested (Timestamp for when the OI task was "Ready to Send")' 
,MIN(CONVERT(DATETIME,O.[Start Date])) 'Documents Requested (Timestamp for when the OI task was communicated to the FI)' 
,COALESCE(MIN(CONVERT(DATETIME,A.[Timestamp])),NULL) 'Attestation sent to the FI'
,'' 'PR sent to the FI' 
,'PRIMARY FI: '+PR.[FI Name] +' AND NON-PRIAMARY FI:'+PR.[Non Primary FI] [PRC RAISED]
,sa.Status
INTO #SLA_ALLFROM LANDING.Import_Entity I
LEFT JOIN [Promotional Keys] P ON I.[Goldtier ID] = P.[End Client ID]
LEFT JOIN SLA_ReadyToSend S ON I.[Goldtier ID] = S.[Entity ID] and  S.[Financial Institution ID] =CASE WHEN I.[Main FI] = 'RM' THEN '863772678'
                                                                        WHEN I.[Main FI] = 'SCB' THEN 836553196
                                                                        WHEN I.[Main FI] = 'STD' THEN 710689297
                                                                        WHEN I.[Main FI] = 'ETH' THEN 309300771
                                                                        WHEN I.[Main FI] = 'AB' THEN    995668333
                                                                            END 
LEFT JOIN attestation_Sent_To_FI A ON I.[Goldtier ID] = A.[Entity ID] and CASE WHEN I.[Main FI] = 'RM' THEN 863772678
                                                                        WHEN I.[Main FI] = 'SCB' THEN 836553196
                                                                        WHEN I.[Main FI] = 'STD' THEN 710689297
                                                                        WHEN I.[Main FI] = 'ETH' THEN 309300771
                                                                        WHEN I.[Main FI] = 'AB' THEN    995668333
                                                                            END = A.[FI ID]
LEFT OUTER JOIN DAILY_OUTSTANDING_STATS O ON I.[Goldtier ID] = O.[End Client Org ID Number] and CASE WHEN I.[Main FI] = 'RM' THEN '863772678'
                                                                        WHEN I.[Main FI] = 'SCB' THEN '836553196'
                                                                        WHEN I.[Main FI] = 'STD' THEN '710689297'
                                                                        WHEN I.[Main FI] = 'ETH' THEN '309300771'
                                                                        WHEN I.[Main FI] = 'AB' THEN    '995668333'
                                                                            END = O.[FI ID]
LEFT OUTER JOIN SA_MASTER_BASE SA ON I.[Goldtier ID] = SA.[GoldTier ID] AND SA.Status <> 'CLOSED' AND CASE WHEN I.[Main FI] = 'RM' THEN '863772678'
                                                                        WHEN I.[Main FI] = 'SCB' THEN '836553196'
                                                                        WHEN I.[Main FI] = 'STD' THEN '710689297'
                                                                        WHEN I.[Main FI] = 'ETH' THEN '309300771'
                                                                        WHEN I.[Main FI] = 'AB' THEN    '995668333'
                                                                            END = SA.[FI ID]
LEFT OUTER JOIN CONSENT_RECEIVED C ON I.[Goldtier ID] = C.[Entity ID] AND C.[Financial Institution ID] = CASE WHEN I.[Main FI] = 'RM' THEN '863772678'
                                                                        WHEN I.[Main FI] = 'SCB' THEN '836553196'
                                                                        WHEN I.[Main FI] = 'STD' THEN '710689297'
                                                                        WHEN I.[Main FI] = 'ETH' THEN '309300771'
                                                                        WHEN I.[Main FI] = 'AB' THEN    '995668333'
                                                                        END
LEFT JOIN PRC PR ON I.[Goldtier ID] = PR.[GoldTier ID]
WHERE 1=1 
AND @SLA_MONTH = MONTH(I.[Date and Time notification recieved from FI to upload entities to TR])GROUP BY[Main FI],Batchno,[Legal Entity Name],I.[Goldtier ID],SA.Status,[Date and Time notification recieved from FI to upload entities to TR],PR.[FI Name],PR.[Non Primary FI]GO

Я также проверил свое маленькое время на своем ноутбуке, оно установлено как yyyy / mm / dd.

Большое спасибо за помощь.

0 ответов