Error when attempting to set variables value

Question

Error when attempting to set variables value

david.dartnell

Ten Centuries

Points: 1276
More actions
May 28, 2014 at 1:29 am

#291737

Hi Everyone,
I am getting the error 'Incorrect syntax near the keyword set' with the following code snippet -
declare @dteTo Date
set @dteTo = CONVERT(date, GETDATE())
My intention is to set the variable @dteTo to the current date (no time component). If I run a SELECT CONVERT(date, GETDATE()) command I do get what I want, it is just assigning the value to the variable that isn't working as intended.
If anybody can offer any suggestions they will be greatly appreciated.
Kind Regards,
Davo

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

david.dartnell (5/28/2014)
Hi Everyone,
I am getting the error 'Incorrect syntax near the keyword set' with the following code snippet -
declare @dteTo Date
set @dteTo = CONVERT(date, GETDATE())
My intention is to set the variable @dteTo to the current date (no time component). If I run a SELECT CONVERT(date, GETDATE()) command I do get what I want, it is just assigning the value to the variable that isn't working as intended.
If anybody can offer any suggestions they will be greatly appreciated.
Kind Regards,
Davo

Quick question as this code runs without an error on SQL 2008R2, what is the problem?

😎

declare @dteTo Date

set @dteTo = CONVERT(date, GETDATE())

select @dteTo

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 2

declare @dteTo Date

set @dteTo = CAST(GETDATE() AS DATE)

TA DA

DONE!! :w00t: 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

harsimranjeetsinghwasson (5/28/2014)
declare @dteTo Date
set @dteTo = CAST(GETDATE() AS DATE)
TA DA
DONE!! :w00t: 🙂

Or even simpler.

declare @dteTo Date

set @dteTo = GETDATE()

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110640 More actions · Answer 4

It's even simpler:

DECLARE @dteTo AS date = GETDATE();

There's no need for a CAST or a CONVERT, as implicit conversion works just fine for this purpose.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

sgmunson (5/28/2014)
It's even simpler:
DECLARE @dteTo AS date = GETDATE();
There's no need for a CAST or a CONVERT, as implicit conversion works just fine for this purpose.

Is there an echo in here? This is the same thing I posted this morning. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Sean Lange (5/28/2014)
sgmunson (5/28/2014)
It's even simpler:
DECLARE @dteTo AS date = GETDATE();
There's no need for a CAST or a CONVERT, as implicit conversion works just fine for this purpose.
Is there an echo in here? This is the same thing I posted this morning. 😉

Almost.... an echo would have required you to have removed the CR/LF and the "set @dteTo",

along with the CAST, but in that case, the echo never would have occurred, so you would have

a time paradox. Dangerous stuff, my man.... dangerous stuff... 😉

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

david.dartnell Ten Centuries Points: 1276 More actions · Answer 7

Quick question as this code runs without an error on SQL 2008R2, what is the problem?
😎
declare @dteTo Date
set @dteTo = CONVERT(date, GETDATE())
select @dteTo

This code works a charm! Thank you 🙂

david.dartnell Ten Centuries Points: 1276 More actions · Answer 8

Hi All,

Thank you very much for your input. I am actually just using getdate() by itself now, without any cast or convert. My error was of the 'human' type, I had forgotten a single talking mark in the code directly above where I saw my error.

On the upside I now have a better understanding of the getdate() function thanks to the responses here.

Kind Regards,

Davo