How can I exclude the penultimate number from a period?

Asked

Viewed 75 times

4

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

I wonder if someone could help me?

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

I tried the LEN, tried the REPLACE, but nothing certain.

sql database sql-server
  • 1

    select left('201601', 4) + right('201601', 1)

  • Where I fit this select?

  • I edited my answer

  • @tcm, what is sgbd? There are functions that facilitate conversion of dates, but that vary from sgbd to sgbd.

  • Sqlserver is the SGBD

3 answers

3

MONTH will return an integer, so in the conversion the 0 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;

  • Where I fit the select?

2

To convert a date to aaaamm, numeric format, there are a few shapes.

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

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

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

1

If your goal is to always remove the 0 on the left, just use the DAY, he returns a int of the value passed, as the int always sets 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 as 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)

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.