asked on

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

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

cyberkiwi

convert(char(8), getdate(), 112)

cyberkiwi

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')

cyberkiwi

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')

Leogal

ASKER

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.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leogal

ASKER

Awesome! Thank you very much!!