将varchar值'/'转换为数据类型int时转换失败。 [英] Conversion failed when converting the varchar value '/' to data type int.

问题描述

我是SQL的新手，我在任务上遇到了一些麻烦。目标是创建一个接受日期时间数据类型和整数的函数，并根据输入的整数将日期时间转换为不同的格式。我收到错误'转换varchar值时转换失败'/';数据类型int。'我完全被难倒了。我不明白/来自哪里或为什么函数试图将其转换为整数。任何建议将不胜感激。

I am very new to SQL and I'm having some trouble on an assignment. Goal is to create a function that accepts a datetime data type and an integer and converts the datetime to a different format based on which integer is entered. I am getting the error 'Conversion failed when converting the varchar value '/' ; to data type int.' and I am completely stumped. I don't understand where the / came from or why the function is trying to convert it to an integer. Any suggestions would be greatly appreciated.

Create function fnDateTime (@type int, @datetime datetime)
Returns varchar(32)
as
Begin
declare @convertedtype varchar(32)
declare @year varchar(4), @month int, @day int, @hour int, @minute int, @second int
    Set @year = DATEPART(YEAR,@datetime)
    Set @month = DATEPART(Month,@datetime)
    Set @day = DATEPART(DAY,@datetime)
    Set @hour = DATEPART(Hour,@datetime)
    Set @minute = DATEPART(Minute,@datetime)
    Set @second = DATEPART(Second, @datetime)
If @type = 1
    Begin
        If  @month < 10 Set @month = '0' + @month
        else set @month = @month
        If @day < 10 Set @day = '0' + @day
        else set @day = @day
        Set @convertedtype = @month + '/' + @day + '/' + @year
    End
Else If @type = 2
    Begin
        If @month = 1 set @month = 'Jan'
        else if @month = 2 set @month = 'Feb'
        else if @month = 3 set @month = 'Mar'
        else if @month = 4 set @month = 'Apr'
        else if @month = 5 set @month = 'May'
        else if @month = 6 set @month = 'Jun'
        else if @month = 7 set @month = 'Jul'
        else if @month = 8 set @month = 'Aug'
        else if @month = 9 set @month = 'Sep'
        else if @month = 10 set @month = 'Oct'
        else if @month = 11 set @month = 'Nov'
        else if @month = 12 set @month = 'Dec'
        If @day < 10 Set @day = '0' + @day
        else set @day = @day
        Set @convertedtype = @month + ' ' + @day + '/' + @year
    End
Else If @type = 3
    Begin
        If  @month < 10 Set @month = '0' + @month
        else set @month = @month
        If @day < 10 Set @day = '0' + @day
        else set @day = @day
        Set @year = SUBSTRING(CONVERT(VARCHAR(4),DATEPART(yy, @datetime)),3,2)
        Set @convertedtype = @month + '/' + @day + '/' + @year
    End
Else If @type = 4
    Begin
        If  @month < 10 Set @month = '0' + @month
        else set @month = @month
        If @day < 10 Set @day = '0' + @day
        else set @day = @day
        Set @convertedtype = @month + '/' + @day + '/' + @year + ' ' +
            @hour + ':' + @minute + ':' + @second
    End
Else If @type = 5
    Begin
        If @hour < 12
        set @convertedtype = @month + '/' + @day + '/' + @year + ' ' +
            @hour + ':' + @minute + ':' + @second + ' ' + 'AM'
        Else If @hour = 12 set @convertedtype = @month + '/' + @day + '/' +
        @year + ' ' + @hour + ':' + @minute + ':' + @second + ' ' + 'PM'
        Else If @hour > 12
        set @hour = (@hour -12)
        Set @convertedtype = @month + '/' + @day + '/' +
        @year + ' ' + @hour + ':' + @minute + ':' + @second + ' ' + 'PM'
    End
Else If @type = 6
    Begin
        If @month = 1 set @month = 'January'
        else if @month = 2 set @month = 'February'
        else if @month = 3 set @month = 'March'
        else if @month = 4 set @month = 'April'
        else if @month = 5 set @month = 'May'
        else if @month = 6 set @month = 'June'
        else if @month = 7 set @month = 'July'
        else if @month = 8 set @month = 'August'
        else if @month = 9 set @month = 'September'
        else if @month = 10 set @month = 'October'
        else if @month = 11 set @month = 'November'
        else if @month = 12 set @month = 'December'
        If @day < 10 Set @day = '0' + @day
        else set @day = @day
        Set @convertedtype = @day + ' ' + @month + ' ' + @year
    End
Else Return 'Please choose a data type with a matching integer 1-6'

Return @convertedtype

End

推荐答案

这是因为DATEPART返回一个Integer，所以你不能使用+来添加字符串。在一个方面，你做得对。你有：

It is because DATEPART returns an Integer so you can't use + to add in strings. In one part you are doing it right. You have:

CONVERT(VARCHAR(4),DATEPART(yy, @datetime)),3,2

你需要到处都这样做。创建一个nvarchar（50）类型的变量并且只为每个变量转换一次可能会更容易。

You'll need to do that everywhere. It would likely be easier to create a variable of type nvarchar(50) and just convert it once for each one.

这里的问题是DataType不匹配。你声明为int的变量，但后来你使用了与varchar相同的变量。



例如你已经将@month声明为整数但稍后你将它用作VARCHAR 。这是不正确的。

The problem here is DataType mismatching. A variable you declared as int but later you have used the same variable as varchar.

For example you have declared @month as integer but later you are using it as VARCHAR. This is incorrect.

If @month = 1 set @month = 'Jan'

您需要重新编写纠正数据类型不匹配的过程。

You need to re-write the procedure correcting data type mismatching.

这篇关于将varchar值'/'转换为数据类型int时转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！