Datetime Parameter Format

Hi,

I'm trying to test a stored procedure in VB and SQL Express 2005 - it has a smalldatetime field.

It works fine from the VB side using the data source Preview Data facility but when I try it in SQL Management Studio I get errors no matter what format I try!

I'm sure I'm missing something very simple - thanks in advance.

USE [Bookings]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[spAddReservation]
@.RES_TBL_ID = 1,
@.RES_TTL_ID = 1,
@.RES_Diner_Surname = N'Bloggs',
@.RES_Date = 28/05/2007 18:15:00,
@.RES_Meal_ID = 1,
@.RES_STA_ID = 1,
@.RES_OCC_ID = 1,
@.RES_STF_ID = 1

SELECT 'Return Value' = @.return_value

GO

The error this generates is
Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '/'.

I've tried with quotes (single or double) but then I get a convert error!
You need to include your date info quotes
You could set current datetime format by SET DATEFORMAT statement.
Also you could use CONVERT function:
@.Res_date = convert(datetime, '28/05/2007 18:15:00', 131)

|||

You won't get datetime conversion errors if you were to use the ISO standard date format = year/month/day,

e.g., '2007/05/28 18:15:00'

|||

See function CONVERT in BOL. If you use styles 112 (ISO) or 126 (ISO8601), then SQL Server will interprete correctly datetime constants no matter the settings for LANGUAGE and DATEFORMAT.

> @.RES_Date = 28/05/2007 18:15:00

@.RES_Date = '2007-05-28 18:15:00'

AMB

|||

I hope the error you got because of the missing quote..

When I check the dateformat it is DMY. (it may cause /throw another error if the system dateformat is different).

It is not bad idea to use the DATEFORMAT if the entier db uses the single format.

Try the following code..

Code Snippet

SET DATEFORMAT DMY;

USE [Bookings]

GO

DECLARE@.return_value int

EXEC@.return_value = [dbo].[spAddReservation]

@.RES_TBL_ID = 1,

@.RES_TTL_ID = 1,

@.RES_Diner_Surname = N'Bloggs',

@.RES_Date = '28/05/2007 18:15:00',

@.RES_Meal_ID = 1,

@.RES_STA_ID = 1,

@.RES_OCC_ID = 1,

@.RES_STF_ID = 1

SELECT'Return Value' = @.return_value

|||Thanks for this - worked a treat!

I had assumed the date format would be as per culture setting and was misled by the fact that that worked OK inside VB Express.