Home / Questions / 350078
Accepted
Franqo Balsamo
Asked: 2020-04-28 11:23:48 +0800 CST 2020-04-28 11:23:48 +0800 CST

Correct conversion in SQL date

  • 772

I want to display the current month and year in a SQL query, I want it to look like this:

april/2020

I tried this:

SELECT DATENAME(MONTH,GETDATE()) + ' / ' + YEAR(GETDATE())

But, I get the following error:

Error de conversión al convertir el valor nvarchar 'Abril / ' al tipo de datos int.

How do I solve it?

sql

4 Answers

  1. It's because YEAR returns integer. You can use the same DATENAME function for the year.

    SELECT DATENAME(MONTH,GETDATE()) + ' / ' + DATENAME(YEAR,GETDATE())
    • 5
  2. Best Answer

    It is important when you need to retrieve system values ​​that depend on a configuration, to set the language/language for which they are required, as the same statement for two different logins can return different results.

    I create a user with a default language of [us-english]

    USE [master]
GO
CREATE LOGIN [userLogin1] WITH PASSWORD=N'userLogin1', 
DEFAULT_DATABASE=[Lab1], 
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Lab1]
GO
CREATE USER [userLogin1] FOR LOGIN [userLogin1]
GO
USE [Lab1]
GO
ALTER USER [userLogin1] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Lab1]
GO
ALTER ROLE [db_owner] ADD MEMBER [userLogin1]
GO

    I create a user with a default language set to [Spanish]

    USE [master]
GO
CREATE LOGIN [userLogin2] WITH PASSWORD=N'userLogin2',
DEFAULT_DATABASE=[Lab1], DEFAULT_LANGUAGE=[Español], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Lab1]
GO
CREATE USER [userLogin2] FOR LOGIN [userLogin2]
GO
USE [Lab1]
GO
ALTER USER [userLogin2] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Lab1]
GO
ALTER ROLE [db_owner] ADD MEMBER [userLogin2]
GO

    Now running one of the queries with no language set returns different results for the two users.

    User us-English

    Spanish User

    The option is to set the appropriate language, regardless of the connection.

    SET LANGUAGE spanish;
DECLARE @DATE DATE;
SET @DATE = GETDATE();
SELECT CONCAT(DATENAME(MONTH,@DATE),' / ',DATENAME(YEAR,@DATE));

    User userLogin1

    User userLogin2

    SetLanguage

    • 3

  3. YEARreturns an integer, so you could use it CONCATto join the data.

    Example:

    SELECT CONCAT(DATENAME(MONTH,GETDATE()), ' / ', YEAR(GETDATE()))

    Demo

    • 2

  4. SQL Server cannot implicitly concatenate a string with an integer, you can do it like this:

    SELECT DATENAME(MONTH,GETDATE()) + ' / ' + CONVERT(VARCHAR,YEAR(GETDATE()))

    or in the way that Luis Cazares mentions.

    • 2