Error converting varchar to Datetime in a SQL Function

Question

I am trying to create a function that converts a given date string to the desired DateTime format. The code is:

Alter function dbo.getDateValue1(@inputdate varchar)
returns varchar
as
begin
declare @inputDateTransformed datetime = CAST(@inputdate AS datetime2)
declare @setDatevalue varchar = convert(varchar,@inputDateTransformed ,112)
return @setDatevalue
end

And I am calling the function as below:

SELECT dbo.getDateValue1('2022-01-01 18:15:15.600' )

Which gives me the error: Conversion failed when converting date and/or time from character string.

Any help would be appreciated.

You declared VARCHAR without a length, which is a bad habit to kick. Here it means VARCHAR(1). Note that using a scalar-valued function for an operation like this is a bad idea prior to SQL Server 2019 (which offers inlining of such functions) as it will tank your performance hard if it's used over multiple rows. — Jeroen Mostert
– Jeroen Mostert, Commented Apr 4, 2022 at 13:12
Why do you need a user defined function to do this anyway? Why not just use CONVERT in the first place? Smells like an XY Problem. — Thom A
– Thom A ♦, Commented Apr 4, 2022 at 13:19
Also, why are you even formatting the date in SQL anyway? Generally this is better to do in the presentation layer (website, app etc.) — DavidG
– DavidG, Commented Apr 4, 2022 at 13:20
Fair, though you might want to therefore try and approach a realistic use case, rather than reinventing the CONVERT function. As @JeroenMostert warned though, scalar functions can provide poor performance; especially when they can't be inlined (due to your version or because they aren't able to be). Inline table value functions are far often a more performant solution, which is also version agnostic. But the reason for your error, as they also noted, is due to the (omission of the) length property of your varchar declarations; which is several places is therefore a varchar(1). — Thom A
– Thom A ♦, Commented Apr 4, 2022 at 13:27
See Bad Habits to Kick : Declaring VARCHAR without (length). — Thom A
– Thom A ♦, Commented Apr 4, 2022 at 13:27

Ramin Faracov · Accepted Answer · 2022-04-05 02:43:49Z

1

Sample:

Create function [dbo].[getDateValue1](@input_date datetime2)  
returns varchar(100)  
begin  
    declare 
    @v_ret varchar(100)

    set @v_ret = CONVERT(varchar, @input_date, 112)
    return @v_ret
end 


SELECT dbo.getDateValue1('2022-01-01 18:15:15.600')
-- Result: 
20220101

answered Apr 5, 2022 at 2:43

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

Bad Habits to Kick : Declaring VARCHAR without (length)

Collectives™ on Stack Overflow

Error converting varchar to Datetime in a SQL Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related