SQLTeam.com | Weblogs | Forums

Casting 0 to a char

Hi,
I have a task in which I have to get a decade out of the year in a form (decade-'s), for example
1995 -> 90's
1983 -> 80's
2005 -> 00's

The last example is where the problem occurs because if I trim first two digits from a year which leaves me with 05 and then trim the 5 I get 0 which when casting to char turns to null value.

Here is my code:

SELECT arg1, arg2, arg3,
CONCAT(cast ( EXTRACT (year from date)%100 as varchar(1)),'0s') AS decade
FROM table1

CAST(YEAR(YourDate)%100/10 AS char(1)) + '0''s'  AS Decade

To be honest, I wouldn't store the Decade as a char-anything or add that "'s" either. Store it as the TINYINT (1 byte unsigned integer) that it is. It will not only save 4 bytes per row both on disk and in memory, it'll also prevent implicit conversions for future calculations, which will also make your future code much faster.

Also, EXTRACT is not valid in T-SQL and so you must be using some other language. DATEPART() in T-SQL is the near equivalent to extract, though. I'll let you do the necessary conversion. Here's what I would do in T-SQL.

 SELECT Decade = DATEPART(yy,[Date])%100/10*10
   FROM table1
;

That, not withstanding, if you really do need the "'s", then @Ifor 's method will work just fine.