3

The data going in: 

<F1>000001234</F1>
<F2>133228579</F2>
<F3>2011-05-25</F3>
<F4>21-332211</F4>
<F5>TxtMail</F5>
<F6/>
<F7>26/04/2011</F7>
<F8>00:09:13</F8>
<F9>0:00</F9>
<F10/>
<F11/>
<F12>Text Service</F12>
<F13>0294443333</F13>
<F14>TXT</F14>
<F15>FR</F15>
<F16>0.17</F16>

Relevant parts of the stored procedure :

@F1     VARCHAR(24) = NULL, --AccountNumber
@F2     VARCHAR(24) = NULL, --InvoiceNumber
@F3     VARCHAR(24) = NULL, --InvoiceDate
@F4     VARCHAR(24) = NULL, --CallerNumber
@F5     VARCHAR(10) = NULL, --Service
@F6     VARCHAR(10) = NULL, --
@F7     varchar(24) = NULL, --CallDate
@F8     VARCHAR(24) = NULL, --CallTime
@F9     VARCHAR(50) = NULL, --Duration
@F10    VARCHAR(50) = NULL, --
@F11    VARCHAR(10) = NULL, --
@F12    VARCHAR(24) = NULL, --Network
@F13    VARCHAR(24) = NULL, --CallingNumber
@F14    VARCHAR(10) = NULL, --Type
@F15    VARCHAR(10) = NULL, --TypeName
@F16    MONEY       = NULL, --Amount

DECLARE @Date VARCHAR(20)
SET @Date = RIGHT(@F7,4)+'/'+SUBSTRING(@F7,4,2)+'/'+LEFT(@F7,2)

-- Combine the date and time into a datetime data type
-- For Time 
DECLARE @time DATETIME
SET @time =  CONVERT(DATETIME, @Date + ' ' + @F8)

The error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This is the only invocation of CONVERT(DATETIME) and I don't use CAST

If I just pass in the data directly, the row gets inserted. If I run the sproc, it goes out-of-range.

Cœur
  • 37,241
  • 25
  • 195
  • 267
CodeMinion
  • 653
  • 2
  • 10
  • 24
  • 1
    could it be related to your regional setting in SQL server where it's a different format for date than the way you are getting "in the data directly"? – Arthur Frankel Jul 15 '11 at 00:04
  • they are both run on the same computer, and I run the Sproc as a plain SQL query declaring F1-16 and setting values when I 'pass in the data directly'. But you could be on to something – CodeMinion Jul 15 '11 at 00:07
  • I guess I would try passing in the hardcoded date time right into the CONVERT function in a simple sproc just to rule out certain things...then try different formats if one doesn't work. Just to get down to the bottom of the real issue. – Arthur Frankel Jul 15 '11 at 00:26
  • I just went and tried what you said, stripped everything out except forming the @time, everything else was static values. Still errors. Then i stripped that part out and passed everything in the sproc as static values and the error goes away. Looks like something definitely is not liking the format being passed in – CodeMinion Jul 15 '11 at 00:54

2 Answers2

4

The safe datetime string formats to use in SQL Server is

YYYYMMDD HH:MM:SS or YYYY-MM-DDTHH:MM:SS.

The conversion you have will fail if SET DATEFORMAT is dmy. SET LANGUAGE will automatically set date format for you. I think both german and norwegian use dmy.

This will fail:

set language norwegian

declare @F7 varchar(10) = '26/04/2011'
declare @F8 varchar(10) = '00:09:13'

DECLARE @Date VARCHAR(20)
SET @Date = RIGHT(@F7,4)+'/'+SUBSTRING(@F7,4,2)+'/'+LEFT(@F7,2)

DECLARE @time DATETIME
SET @time =  CONVERT(DATETIME, @Date + ' ' + @F8)

Do like this instead (using YYYY-MM-DDTHH:MM:SS) to be safe regardless of language/dateformat settings.

declare @F7 varchar(10) = '26/04/2011'
declare @F8 varchar(10) = '00:09:13'

DECLARE @Date VARCHAR(20)
SET @Date = RIGHT(@F7,4)+'-'+SUBSTRING(@F7,4,2)+'-'+LEFT(@F7,2)

DECLARE @time DATETIME
SET @time =  CONVERT(DATETIME, @Date + 'T' + @F8)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
1

Try the following and see what you are getting,

DECLARE @Date VARCHAR(20)
SET @Date = RIGHT('26/04/2011',4)+'/'+SUBSTRING('26/04/2011',4,2)+'/'+LEFT('26/04/2011',2)

-- Combine the date and time into a datetime data type
-- For Time 
DECLARE @time DATETIME
SET @time =  CONVERT(DATETIME, @Date + ' ' + '00:09:13')

print @time
sudheshna
  • 1,150
  • 1
  • 13
  • 24