T-sql 2015 cast and convert problem

Transact-SQL
1

I have the following t-sql 2012, which is the following:

DECLARE @endYear varchar(04) = 2016
select convert(smalldatetime, ('07/31/' + convert(char(02), @endYear - 1)))
select convert(smalldatetime, '08/01/' + @endYear )

that is having a conversion error.

I want one select statement to be 07/31/2015 and the other date to be

                                              08/01/2016 in a smalldatetime format.

Thus can you show me the sql to accomplish my goal?

2

It looks like the issue is that you are performing arithmetic on a string (@endYear - 1). Also, it's better to provide the date string in the format of YYYYMMDD.[code]DECLARE @endYear varchar(04) = 2016;

select convert(smalldatetime, cast(cast(@endYear as int) - 1 as varchar(10)) + '/07/31')
select convert(smalldatetime, @endYear + '/08/01')[/code]

3 
select cast(@endYear + '0731' as smalldatetime),
       cast(@endYear + '0801' as smalldatetime)
4

You can't do:

convert(char(02), @endYear - 1)

because it will exceed the precision of the calculation (which needs 4 digits, and you are only providing CHAR(2))

Example:

DECLARE @endYear varchar(04) = 2016
select convert(char(02), @endYear - 1)

But as others have said you should not use mm/dd/yy style date formats in conversions, they are ambiguous and SQL may well handle them differently in the future if something changes - such as the language of the currently connected user.

Try this:

SET LANGUAGE French
GO
SELECT CONVERT(datetime, '12/31/2016') -- This will raise an error
GO
SET LANGUAGE English
GO
SELECT CONVERT(datetime, '12/31/2016')
GO

Stick to using 'yyyymmdd' format for string-to-date conversions as SQL will always treat a string of 8 digits as being this, unambiguous, format - regardless of locale / language / whatever settings and options.

An 8 digit string will even be treated as 'yyyymmdd' if you explicitly try to override the conversion with

SET DATEFORMAT mdy

and so on and such forth ...

2 Likes