udf to convert string to date problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

udf to convert string to date problem

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-06-21 : 07:37:03

hi

I have a date column in the format (15 Jan 2012) stored as a varchar in sql server. I am trying to convert it to a date and have created a udf below. It works but when I run it, it will only convert to the date format 2012-01-15. What I expect is 15/01/2012 as this works if I just use the select statement below, not from within a function.

any ideas

thanks





ALTER FUNCTION [dbo].[udf_convertdate]
(
    @DateColumn varchar (11) 
)
RETURNS varchar (10)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Output date
    
  SELECT @Output =  Case When isdate(@DateColumn) = 1
THEN   CONVERT(varchar(11),CAST(@DateColumn AS DATE),103) 
Else Null   End 
 RETURN  @Output

END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-21 : 08:02:48

quote:
Originally posted by spinningtop

hi

I have a date column in the format (15 Jan 2012) stored as a varchar in sql server. I am trying to convert it to a date and have created a udf below. It works but when I run it, it will only convert to the date format 2012-01-15. What I expect is 15/01/2012 as this works if I just use the select statement below, not from within a function.

any ideas

thanks

ALTER FUNCTION [dbo].[udf_convertdate]
(
@DateColumn varchar (11)
)
RETURNS varchar (10)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Output date

SELECT @Output = Case When isdate(@DateColumn) = 1
THEN CONVERT(varchar(11),CAST(@DateColumn AS DATE),103)
Else Null End
RETURN @Output

END

ALTER FUNCTION [dbo].[udf_convertdate]
(
    @DateColumn varchar (11) 
)
RETURNS varchar (10)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Output date VARCHAR(10)
    
  SELECT @Output =  Case When isdate(@DateColumn) = 1
THEN   CONVERT(varchar(11),CAST(@DateColumn AS DATE),103) 
Else Null   End 
 RETURN  @Output

END

While that would fix the issue that you are trying to solve, I wonder about the reasons for doing this type of conversion in SQL Server. Many experts would advise you to store dates in DATETIME (or another date/time format) and do the conversions in the client applications.

spinningtop
Starting Member

29 Posts

Posted - 2012-06-21 : 10:06:33

Thanks but I have already tried ouputting to varchar and it works but its not really what I want as I want to do calculations on the dates.

The data at source is from a text file with the format (1 Jan 2012) I was trying to convert to date on import in SSIS but I was having all sorts of problems with this format so I ended up importing as a straight varchar and trying to convert in SQL server with a udf.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources