How can I exclude the penultimate number of a period?

Question

How can I exclude the penultimate number of a period?

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (1 votes)

4

I'm trying to leave the period from 201601 to 20161 in SQL, but I can not do it at all!

Can anyone help me?

SELECT CONVERT(NUMERIC(5), CONVERT(VARCHAR(4), YEAR(Periodo_Mensal))+ 
       CONVERT(VARCHAR(4), MONTH(Periodo_Mensal))) AS PERIODO_MENSAL

I tried LEN , tried REPLACE , but nothing was right.

sql sql-server

asked by anonymous 01.11.2016 / 20:51

3 answers

Get Id and move to another function on the click button How do I create a Library-Android using Android Studio?

score 3 · Answer 1

MONTH will return an integer, so in the conversion the 0 on the left will disappear.

declare @hoje datetime,
        @periodo varchar(6);

set @hoje = '2016-01-01';

-- Pega o ano + o mês
set @periodo = cast(year(@hoje) as varchar) + cast(month(@hoje) as varchar);

select @periodo as periodo_mensal;

score 2 · Answer 2

To convert a date to aaaamm, numeric format, there are some ways.

-- código #1
SELECT (YEAR(Periodo_Mensal) * 100 + 
        MONTH(Periodo_Mensal)) as PERIODO_MENSAL
  from ...

If you need the result to be string , you can have

-- código #2
SELECT Convert(char(6), (YEAR(Periodo_Mensal) * 100 + 
                         MONTH(Periodo_Mensal))
              ) as PERIODO_MENSAL
      from ...

score 1 · Answer 3

If your goal is to always remove 0 from the left, just use DAY , it returns a int of the passed value, as int always matches the 0 to the left you will have removed it by default.

declare @hoje datetime = '20160101';
select cast(year(@hoje) as varchar) + cast(month(@hoje) as varchar) 
+ cast(day(@hoje) as varchar)

Or according to the comment below (He only wants the year and the month - Sorack 1 min ago), the month () , also has the same function as DAY.

declare @hoje datetime = '20160101';
select cast(year(@hoje) as varchar) + cast(month(@hoje) as varchar)