0

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.

qudsif
  • 39
  • 4
  • 6
    You declared `VARCHAR` without a length, which is a [bad habit to kick](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). 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 Apr 04 '22 at 13:12
  • 2
    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](//xyproblem.info). – Thom A Apr 04 '22 at 13:19
  • 2
    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 Apr 04 '22 at 13:20
  • @Larnu Just practicing UDFs in SQL. – qudsif Apr 04 '22 at 13:23
  • 3
    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 Apr 04 '22 at 13:27
  • 1
    See [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). – Thom A Apr 04 '22 at 13:27
  • 1
    Forget about the function; [this has nothing to do with the function](https://dbfiddle.uk/?rdbms=sqlserver_2017l&fiddle=9da2efabd764f313cb000ad2d405e272). – Aaron Bertrand Apr 04 '22 at 13:49
  • Wouldn't it be better to just store the value as `datetime` in the first place, then it doesn't need formatting – Charlieface Apr 04 '22 at 14:04

1 Answers1

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
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8
  • [Bad Habits to Kick : Declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – Charlieface Apr 05 '22 at 10:08