CODEWITHSUNDEEP
sql-servert-sql
ngoc
ngoc

Reputation: 63

SQL Convert date

declare @vaultdate varchar(10)
set @vaultdate = convert(date, getdate())-1
print @vaultdate

I got an error

Operand type clash: date is incompatible with int

Can someone see what I have done wrong? Thank you

Upvotes: 0

Views: 149

Answers (4)

sandeep sharma
sandeep sharma

Reputation: 70

I have updated the query, this will give you yyyy-mm-dd format

declare @vaultdate varchar(12)

set @vaultdate = getdate()-1

print CAST(convert(varchar,@vaultdate,20) AS DATE)

Upvotes: 0

Mack
Mack

Reputation: 2552

Gordon Linoff is totally correct when working with dates use date datatypes. Using correct datatypes is one of the foundations of good SQL design.

If you need to convert the date to a string this should be done at point of use i.e. the presentation layer otherwise you can end up converting the date to a varchar for presentation then the presentation layer converting it to a separate format again because the requirements changed.

If you absolutely have to use a varchar then just add a couple of lines to Gordons very correct answer.

declare @vaultdatestring varchar(10);
declare @vaultdate date;
set @vaultdate = dateadd(day, -1, convert(date, getdate()));

print @vaultdate;

set @vaultdatestring = convert(varchar(10), @vaultdate,103)
print @vaultdatestring;

There is a good resource on date formatting here: Date and Time Conversions Using SQL Server

If you could tell us why the varchar is needed we may be able to provide better suggestions.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Presumably, you are using SQL Server. If you want to work with dates, then use dates and their functions:

declare @vaultdate date;
set @vaultdate = dateadd(day, -1, convert(date, getdate()));

print @vaultdate;

Upvotes: 2

apomene
apomene

Reputation: 14389

You have 1 parenthesis wrong and also you need to declare your variable as datetime. You don't even need convert:

declare @vaultdate datetime
set @vaultdate = getdate()-1
print @vaultdate

Upvotes: 0

Related Questions