CODEWITHSUNDEEP
sqlsql-server
rinuthomaz
rinuthomaz

Reputation: 1413

SQL Server : function to change the date format yyyymmddThhmmssZ

Created a function to change the date to this format yyyymmddThhmmssZ

Here is the function

    USE [IMT]
GO

/****** Object:  UserDefinedFunction [dbo].[GetForamttedDate]    Script Date: 09/05/2014 12:46:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      rthomas
-- Create date: 04/08/14
-- =============================================
ALTER FUNCTION [dbo].[GetForamttedDate]
(
    -- Add the parameters for the function here
    @InitialDate smalldatetime
)
RETURNS VARCHAR
AS
BEGIN
Declare  @FormatDateInitial varchar(50) 
Declare  @FormatDate varchar(50)
Declare  @FormatDateFinal VARCHAR(50)
Declare @Debug      BIT = 0

SET @FormatDateInitial =  (SELECT convert(varchar(30), @InitialDate,121))
SET @FormatDate =(SELECT SUBSTRING(@FormatDateInitial,1,4)+SUBSTRING(@FormatDateInitial,6,2)+SUBSTRING(@FormatDateInitial,9,2)+' '+SUBSTRING(@FormatDateInitial,12,2)+SUBSTRING(@FormatDateInitial,15,2)+SUBSTRING(@FormatDateInitial,18,2))
SET @FormatDateFinal= STUFF(@FormatDate,9,1,'T')+'Z'
return @FormatDateFinal

END

GO

And it is returning 20140805T121109Z

But when I use this function for a date column in the table it returns only one character. 

select function_name(date_column) from table

returns only 2

Please help me

Upvotes: 2

Views: 492

Answers (1)

marc_s
marc_s

Reputation: 755147

The problem is this:

ALTER FUNCTION [dbo].[GetForamttedDate] (@InitialDate smalldatetime)
RETURNS VARCHAR

This RETURNS VARCHAR will give you a default length of 1 character !

You should get in the habit of always explicitly defining a valid length for any VARCHAR parameter and variable whenever you use one!

Please see (and embrace!) this blog post on that topic

Upvotes: 1

Related Questions