How to pass 'GETDATE()' as SqlParameter

Thanks for the response Ekrem. I had considered that as a solution but I
would like to ensure that date/time is accurate by using the date/time of the
server. I don't have control of all the clients environment so it's possible
for the client date/time to be incorrect or in a different time zone.


"Ekrem Önsoy" wrote:

> If you write your codes as the following:
>
> sqlp(1) = New System.Data.SqlClient.SqlParameter("@currentdatetime",
> "GETDATE()")
>
> Then "GETDATE()" will be a string and your app (probably) raise an error as
> @currentdatetime will not be a valid datetime (but your app probably expects
> a datetime data type) value but a string. It'll be a string when you put
> something in quotation marks. Instead, if you write your codes using VB.Net
> you should use the following:
>
> ("@currentdatetime", Date.Today)
>
> Then, @CurrentDateTime parameter's value will be the date of today.
> Getdate() is a function in SQL Server, not in VB.Net for instance...
>
> --
> Ekrem Önsoy
>
>
>
> "Clay" wrote in message
>
> > Is it possible to pass the function GETDATE() as a SqlParameter?
> >
> > I would like to change:
> > "UPDATE tablename SET [deleted]=1, [date_modified]=GETDATE(),
> > [user_modified]=@user WHERE id=@id"
> >
> > To:
> > "UPDATE tablename SET [deleted]=1, [date_modified]=@currentdatetime,
> > [user_modified]=@user WHERE id=@id"
> >
> > With:
> > sqlp(1) = New System.Data.SqlClient.SqlParameter("@currentdatetime",
> > "GETDATE()")
> >
>
 >> Stay informed about: How to pass 'GETDATE()' as SqlParameter 

On Mon, 31 Dec 2007 07:27:01 -0800, Clay wrote:

>Thanks for the response Ekrem. I had considered that as a solution but I
>would like to ensure that date/time is accurate by using the date/time of the
>server. I don't have control of all the clients environment so it's possible
>for the client date/time to be incorrect or in a different time zone.

Hi Clay,

In that case, you have to make sure that GETDATE() (or rather,
CURRENT_TIMESTAMP), runs at the server.

Best way is to create a stored procedure that you call from the client.
Give it an optional paramter and set it to CURRENT_TIMESTAMP if not
filled from the client:

CREATE PROCEDURE MyProc
@TheDate datetime
AS
BEGIN;
IF @TheDate IS NULL
BEGIN;
SET @TheDate = CURRENT_TIMESTAMP;
END;
-- Do something with @TheDate
END;

If you have to send a query string (please don't if you can avoid it!!),
then you can use COALESCE to replace a null value with CURRENT_TIMESTAMP

SET date_modified = COALESCE(@currentdatetime, CURRENT_TIMESTAMP)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: How to pass 'GETDATE()' as SqlParameter