sql server new users:
Perhaps I just didn't get enough sleep last night or something .. BUT,
I'm having some trouble with this line of my SQL:
AND [date] < DATEADD(DAY, 2007-09-10 11:39:10.053, GETDATE())
This returns this error:
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '11'.
I tried single quotes to enclose the date/time info, like so:
AND [date] < DATEADD(DAY, '2007-09-10 11:39:10.053', GETDATE())
but that returns this error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of dateadd function.
I tried something like this:
AND [date] < DATEADD(DAY, CONVERT(DATETIME, '2007-09-10 11:39:10.053', 101),
GETDATE())
But, using that, I get the same error as before:
Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 2 of dateadd function.
The only way this thing will work is if I remove the Time portion so it
looks like this:
AND [date] < DATEADD(DAY, 2007-09-10, GETDATE())
So, how can I get this to work while including the entire date/time of
2007-09-10 11:39:10.053 ?
TIA,
Jim
I believe you're right Roy. DateDiff is what I was looking for, not dateadd. I do use a few DateAdd's in my query so apparently I should have had some coffee this morning along with a few more hours of sleep last night. Thanks. [quoted text, click to view] "Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:jreqg39ch1kijgjk5jg52pp12kserv4jml@4ax.com...
> DATEADD lets you add or subtract a number of days (or other units) to
> a datetime. The second parameter has to be that number - an integer -
> not a datetime.
>
> Perhaps you did not intend to add two datetimes, but instead calculate
> the difference? That would be DATEDIFF. DATEDIFF takes the same
> first parameter for the unit, and two datetimes as the other
> parameters.
>
> If DATEDIFF is not the answer, what exactly are you trying to do?
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 10 Oct 2007 12:23:45 -0700, "Jim in Arizona"
> <tiltowait@hotmail.com> wrote:
>
>>Perhaps I just didn't get enough sleep last night or something .. BUT,
>>I'm having some trouble with this line of my SQL:
>>
>>AND [date] < DATEADD(DAY, 2007-09-10 11:39:10.053, GETDATE())
>>
>>This returns this error:
>>
>>Msg 170, Level 15, State 1, Line 8
>>Line 8: Incorrect syntax near '11'.
>>
>>I tried single quotes to enclose the date/time info, like so:
>>
>>
>>AND [date] < DATEADD(DAY, '2007-09-10 11:39:10.053', GETDATE())
>>
>>but that returns this error:
>>
>>Msg 8116, Level 16, State 1, Line 1
>>Argument data type varchar is invalid for argument 2 of dateadd function.
>>
>>I tried something like this:
>>
>>AND [date] < DATEADD(DAY, CONVERT(DATETIME, '2007-09-10 11:39:10.053',
>>101),
>>GETDATE())
>>
>>But, using that, I get the same error as before:
>>
>>Msg 8116, Level 16, State 1, Line 1
>>Argument data type datetime is invalid for argument 2 of dateadd function.
>>
>>The only way this thing will work is if I remove the Time portion so it
>>looks like this:
>>
>>AND [date] < DATEADD(DAY, 2007-09-10, GETDATE())
>>
>>So, how can I get this to work while including the entire date/time of
>>2007-09-10 11:39:10.053 ?
>>
>>TIA,
>>Jim
>>
>>
>>
>>
DATEADD lets you add or subtract a number of days (or other units) to a datetime. The second parameter has to be that number - an integer - not a datetime. Perhaps you did not intend to add two datetimes, but instead calculate the difference? That would be DATEDIFF. DATEDIFF takes the same first parameter for the unit, and two datetimes as the other parameters. If DATEDIFF is not the answer, what exactly are you trying to do? Roy Harvey Beacon Falls, CT On Wed, 10 Oct 2007 12:23:45 -0700, "Jim in Arizona" [quoted text, click to view] <tiltowait@hotmail.com> wrote:
>Perhaps I just didn't get enough sleep last night or something .. BUT,
>I'm having some trouble with this line of my SQL:
>
>AND [date] < DATEADD(DAY, 2007-09-10 11:39:10.053, GETDATE())
>
>This returns this error:
>
>Msg 170, Level 15, State 1, Line 8
>Line 8: Incorrect syntax near '11'.
>
>I tried single quotes to enclose the date/time info, like so:
>
>
>AND [date] < DATEADD(DAY, '2007-09-10 11:39:10.053', GETDATE())
>
>but that returns this error:
>
>Msg 8116, Level 16, State 1, Line 1
>Argument data type varchar is invalid for argument 2 of dateadd function.
>
>I tried something like this:
>
>AND [date] < DATEADD(DAY, CONVERT(DATETIME, '2007-09-10 11:39:10.053', 101),
>GETDATE())
>
>But, using that, I get the same error as before:
>
>Msg 8116, Level 16, State 1, Line 1
>Argument data type datetime is invalid for argument 2 of dateadd function.
>
>The only way this thing will work is if I remove the Time portion so it
>looks like this:
>
>AND [date] < DATEADD(DAY, 2007-09-10, GETDATE())
>
>So, how can I get this to work while including the entire date/time of
>2007-09-10 11:39:10.053 ?
>
>TIA,
>Jim
>
>
>
Don't see what you're looking for? Try a search.
|
