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)cyberspace.org |

[Christopher Benson-Manica]

Christopher Benson-Manica <at***@nospam.cyberspace.org> spoke thus:

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

Okay, this one's straight out of the transact SQL book, and it doesn't work
either...
SET @Date=CAST('1999-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)cyberspace.org |

[Simon Hayes]

"Christopher Benson-Manica" <at***@nospam.cyberspace.org> wrote in message
news:bj**********@chessie.cirr.com...

Christopher Benson-Manica <at***@nospam.cyberspace.org> spoke thus:

DECLARE @Date datetime (right?)
???
EXEC usp_AddRequest 313,'E',@Date,'QUAL'
^^^^^ <- this is the parameter that wants to be a datetime
Okay, this one's straight out of the transact SQL book, and it doesn't

work either...
SET @Date=CAST('1999-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)cyberspace.org |

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