TSQL what is the cleanest way to generate a date format of YYYYMMDD

Leogal
Leogal used Ask the Experts™
on
I am trying to return the value of date inputted as mm-dd-yyyy to an ouput of yyymmdd in  SQL

Here is the function:

Alter function dbo.fn_get_YYYYMMDD

(@FN_date   int)

declare @result    int

set @result  =
       case When @Fn_date <> 01-01-1900  then
      convert (varchar,@FN_date, 1111)
         else 0
  end

Return @Result

End

-----------------------------------------------------------------------------------------------------------------

here is the test
select dbo.FN_Get_YYYYMMDD(08-18-1998)

This is the returned data :  (- 2008) which of course is wrong

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
convert(char(8), getdate(), 112)
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
sample

create function dbo.fn_get_YYYYMMDD
(@FN_date   datetime)
returns char(8)
as
begin
return convert(char(8), @FN_date, 112)
end

select dbo.fn_get_YYYYMMDD('12/31/2030')
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
For what it's worth

select dbo.FN_Get_YYYYMMDD(08-18-1998)

means

select dbo.FN_Get_YYYYMMDD( 08 minus 18 minus 1998 )

08 minus 18 minus 1998 is -2008

The best way to enter a date in Sql Server is in ISO notation YYYYMMDD
e.g.

select dbo.fn_get_YYYYMMDD('20300512')

or based on your language (default us_english) so

select dbo.fn_get_YYYYMMDD('12/31/2030')

Author

Commented:
Cyberkiwi, thanks so very much, most appreciate the point taken on the dates passed, regarding the need for the quotes as stated in your last comment.

Only one piece left how to filer out the 01--01-1900 dates that are invalid in the database and set them to 0.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Are you after yyyymmdd as a 8-char string, or as a 8-digit integer?

Regardless, use this function which returns char(8).  The result is safe to assign to an int variable or int column.

alter function dbo.fn_get_YYYYMMDD
(@FN_date   datetime)
returns char(8)
as
begin
return case when @FN_date=0 then 0 else convert(char(8), @FN_date, 112) end
end
GO

Author

Commented:
Awesome! Thank you very much!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial