Returning a date in format 2012-02-01

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

General SQL Server Forums

New to SQL Server Programming

Returning a date in format 2012-02-01

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2012-03-20 : 09:42:36

I have a function which I need to change. At the moment it returns a date in the format '12/02/2008' but I would like it to return a date as '2012-02-01'.


ALTER FUNCTION [dbo].[anonDOB]
(
-- Add the parameters for the function here
@DOB varchar(10)
)
RETURNS varchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @anonDOB varchar(10), @month varchar(2), @slashes VARCHAR(1)

IF(LEN(@DOB) = 10)
BEGIN
SET @DOB = REPLACE(@DOB, '/','')
SET @slashes = 'Y'
END

SET @month = SUBSTRING(@DOB,3,2)

IF @month IN ('01','02','03','04','05','06','07','08','09','10','11','12')
-- Add the T-SQL statements to compute the return value here
IF @month IN ('01','02','03')
SET @anonDOB = '0102' + RIGHT(@DOB,len(@DOB) - 4)
ELSE IF @month IN ('04','05','06')
SET @anonDOB = '0105' + RIGHT(@DOB,len(@DOB) - 4)
ELSE IF @month IN ('07','08','09')
SET @anonDOB = '0108' + RIGHT(@DOB,len(@DOB) - 4)
ELSE IF @month IN ('10','11','12')
SET @anonDOB = '0111' + RIGHT(@DOB,len(@DOB) - 4)
ELSE SET @anonDOB = NULL
ELSE SET @anonDOB = NULL

IF @slashes = 'Y'
SET @anonDOB = LEFT(@anonDOB, 2) + '/' + SUBSTRING(@anonDOB, 3, 2) + '/' + RIGHT(@anonDOB, 4)

-- Return the result of the function
RETURN @anonDOB

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 09:49:50

your date column in the table is varchar or date/datetime?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

crazyco
Starting Member

30 Posts

Posted - 2012-03-20 : 09:56:17

Could be either or depending on the format we receive the data in. The expected format is yyyymmdd or yyyy-mm-dd as dashes are removed prior to re-formatting.
Slashes are retained, so if yyyy-mm-dd is supplied, then yyyy-dmm-dd is returned.
Likewise, if yyyy-mm-dd is supplied, yyyymmdd is returned.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 10:19:13

So the answer is "Wild West"???

You better check that it is actually a date in the first place

IF ISDATE(@DOB) = 0
BEGIN
..start error handling
END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

crazyco
Starting Member

30 Posts

Posted - 2012-03-21 : 05:08:29

I figured it out myself:

ALTER FUNCTION [dbo].[anonDOB_date]
(
-- Add the parameters for the function here
@DOB varchar(10)
)
RETURNS varchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @anonDOB varchar(10), @month varchar(2), @dashes VARCHAR(1)

IF(LEN(@DOB) = 10)
BEGIN
SET @DOB = REPLACE(@DOB, '-','')
SET @dashes = 'Y'
END

SET @month = SUBSTRING(@DOB,5,2)

IF @month IN ('01','02','03','04','05','06','07','08','09','10','11','12')
-- Add the T-SQL statements to compute the return value here
IF @month IN ('01','02','03')
SET @anonDOB = LEFT(@DOB, 4) + '0201'
ELSE IF @month IN ('04','05','06')
SET @anonDOB = LEFT(@DOB, 4) + '0501'
ELSE IF @month IN ('07','08','09')
SET @anonDOB = LEFT(@DOB, 4) + '0801'
ELSE IF @month IN ('10','11','12')
SET @anonDOB = LEFT(@DOB, 4) + '1101'
ELSE SET @anonDOB = NULL
ELSE SET @anonDOB = NULL

IF @dashes = 'Y'

SET @anonDOB = LEFT(@anonDOB, 4) + '-' + SUBSTRING(@anonDOB, 5, 2) + '-' + RIGHT(@anonDOB, 2)

-- Return the result of the function
RETURN @anonDOB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:17:26

i didnt understand why you want to return dates as varchar and then worry on format? why not return it as date themselves?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources