Simple query

[Christopher Benson-Manica]

I know this is super-basic, but I'm a newbie and I can't get it to work...

I'm trying to call a stored procedure that has a datetime as one of its
parameters. How the heck do I get a datetime?? I'd even settle for knowing I
was declaring variables correctly...

DECLARE @Date datetime (right?)
???
EXEC usp_AddRequest 313,'E',@Date,' QUAL'
^^^^^ <- this is the parameter that wants to be a
datetime

How do I make Date correspond to an actual date/time? How do I assign it to
be equal to SELECT GETDATE()? Why doesn't SET @DATE = SELECT GETDATE() work?
And why the heck is it so hard to find the answers online?? I've Googled
endlessly and found nothing...

--
Christopher Benson-Manica | Jumonji giri, for honour.
ataru(at)cybers pace.org |

[Christopher Benson-Manica]

Re: Simple query

Christopher Benson-Manica <ataru@nospam.c yberspace.org> spoke thus:
[color=blue]
> DECLARE @Date datetime (right?)
> ???
> EXEC usp_AddRequest 313,'E',@Date,' QUAL'
> ^^^^^ <- this is the parameter that wants to be a
> datetime[/color]

Okay, this one's straight out of the transact SQL book, and it doesn't work
either...


SET @Date=CAST('199 9-01-17 11:47:40' AS datetime)

It insists that it can't convert a varchar to a datetime... whyyyyy? :(

--
Christopher Benson-Manica | Jumonji giri, for honour.
ataru(at)cybers pace.org |

[Simon Hayes]

Re: Simple query


"Christophe r Benson-Manica" <ataru@nospam.c yberspace.org> wrote in message
news:bjann8$o7o $1@chessie.cirr .com...[color=blue]
> Christopher Benson-Manica <ataru@nospam.c yberspace.org> spoke thus:
>[color=green]
> > DECLARE @Date datetime (right?)
> > ???
> > EXEC usp_AddRequest 313,'E',@Date,' QUAL'
> > ^^^^^ <- this is the parameter that wants to[/color][/color]
be a[color=blue][color=green]
> > datetime[/color]
>
> Okay, this one's straight out of the transact SQL book, and it doesn't[/color]
work[color=blue]
> either...
>
>
> SET @Date=CAST('199 9-01-17 11:47:40' AS datetime)
>
> It insists that it can't convert a varchar to a datetime... whyyyyy? :(
>
> --
> Christopher Benson-Manica | Jumonji giri, for honour.
> ataru(at)cybers pace.org |[/color]

The format of your date string is not always recognized correctly by SQL
Server:

declare @date datetime

/* Conversion error */
set dateformat dmy
set @date = CAST('1999-01-17 11:47:40' AS datetime)
select @date

/* No conversion error */
set dateformat mdy
set @date = CAST('1999-01-17 11:47:40' AS datetime)
select @date

Both the following formats are 'safe' and always interpreted consistently by
SQL Server, so using either one will work for you:

'1999-01-17T11:47:40'
'19990117 11:47:40'

To answer your previous post, this should work:

DECLARE @Date datetime
SET @Date = getdate()
EXEC usp_AddRequest 313,'E',@Date,' QUAL'

In this case, date formatting is irrelevant because you are always working
with datetime values.

Simon