Trying to assign the value of an SQL Query to a variable SET

Question

I am trying to get date in format yyyymm and then use that date in some operation.

declare @currdate datetime
set @currdate = (SELECT CONVERT(nvarchar(6), GETDATE(), 112))

ERROR:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Or if I use below:

declare @currdate datetime
set @currdate = SELECT FORMAT(GetDate(),'yyyyMM')

I get same error as above.

So I want if today's date is 17-05-2020, I want 202005 as output and i want to store that output in some variable.

ScaisEdge · Accepted Answer · 2020-05-17 08:49:03Z

1

You have declare the var as date then

Or you declare a varchar

    declare @currdate nvarchar(6)
    set @currdate = (SELECT CONVERT(nvarchar(6), GETDATE(), 112))

or as clearly precised by @MartinSmith

it is char(6). It is neither variable length nor requires any characters outside of ASCII digits

    declare @currdate char(6)
    set @currdate = (SELECT CONVERT(nvarchar(6), GETDATE(), 112))

or you use as date

declare @currdate datetime
set @currdate GETDATE(),

edited May 17, 2020 at 8:49

answered May 17, 2020 at 7:50

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

noob Over a year ago

Please read what i want ...i need 202005 as output for current date if today is 17-05-2020

ScaisEdge Over a year ago

then don't declare the var a date but as a nvarchar(6) . 202005 is nvarchar(6) NOT a date

Martin Smith Over a year ago

it is char(6). It is neither variable length nor requires any characters outside of ASCII digits

ScaisEdge Over a year ago

@MartinSmith .. correct .. answer updated with your comment in evidence ..thanks

Gordon Linoff · Accepted Answer · 2020-05-17 12:09:45Z

1

You don't specify if you want the result as a string or a number. As a number, it is easy:

select year(getdate()) * 100 + month(getdate())

This is trivially converted to a string:

select convert(varchar(6), year(getdate()) * 100 + month(getdate()))

However, your format() is just fine. The issue is the destination type -- it should be a string, not a date.

answered May 17, 2020 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

noob Over a year ago

I need only to store this yyyymm into some place

Gordon Linoff Over a year ago

@ShailajaGuptaKapoor . . . I don't understand your comment. Both of these generate a column of the format yyyymm, one as a number and the other as a string.

noob Over a year ago

generation is fine, it was also about storing in some other variable, but thats been answered. Thank

Collectives™ on Stack Overflow

Trying to assign the value of an SQL Query to a variable SET

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related