Setting a variable date in SQL

Question

In SQL Server Management Studio, I am trying to reference a specific date and time, using a variable for the date, as shown below.

Declare @specified_date Date
set @specified_date = '07-01-2013'

Select * 
from etc
Where CreatedDate > CONVERT(datetime, @specified_date & '00:00:00.000' )

This code is not working, and I am receiving this error:

The data types date and varchar are incompatible in the '&' operator.

The data being used has both a date and time code on it, and instead of changing multiple queries, I'd like to be able to just define the date once and have the variable carry it across. If anyone knows of a solution, that would be great.

I would recommend to **always** use a ISO-8601 formatted date, if you specify dates as string - that would be the `YYYY-MM-DD` format. Only that format is guaranteed to work on all SQL Server instances with any language and/or dateformat setting. Anything else *might* work with the right settings, but could fail horribly when used on another system with different settings — marc_s, Jul 30 '13 at 20:28

score 28 · Accepted Answer · answered Jul 30 '13 at 20:10

28

Did you try this:

Declare @specified_date Date
set @specified_date = '07-01-2013'
Select * from etc
Where CreatedDate > @specified_date

answered Jul 30 '13 at 20:10

BWS

3,786
18
25

SO is giving me a hard time adding the code, it's basically declaring one day as A, the next as B, and then selecting * between A and B – Dakota Jul 30 '13 at 20:19
1

Note: if you need to include a time you must use `datetime` or it will get truncated. – Simon_Weaver Mar 12 '21 at 21:43

score 3 · Answer 2 · answered Jul 30 '13 at 20:15

3

Use the + instead of &. You will also have to cast the string to make it a datetime.

Declare @specified_date Date
set @specified_date = '07-01-2013'
Select * from etc
Where CreatedDate > CONVERT(datetime, @specified_date + cast('00:00:00.000' as datetime))

answered Jul 30 '13 at 20:15

Travis Grannan

85
12

1

Thanks! saving this...I'm so used to coding in VBA that it's hard to not concatenate things using a & lol – Dakota Jul 30 '13 at 20:31

Setting a variable date in SQL

2 Answers2