Error converting data type varchar to smalldatetime
I have seen a few answers on the web for this error but I can't seem to get anything to work for me.
Can you help me please?
My output is:
__________________________
execute sp_insertreminderuser 'jo', 'bloggs', '28/10/1965', 'joe@bloggs.com', 'jo', 1
__________________________
The SP is:
__________________________
CREATE PROCEDURE sp_insertreminderuser
@firstname varchar(50),
@lastname varchar(50),
@birthdate smalldatetime,
@email varchar(100),
@password varchar(25),
@sees_public char(1)
AS
INSERT INTO tblusers (firstname, lastname, birthdate, email, password, sees_public)
VALUES (@firstname, @lastname, @birthdate, @email, (convert(varbinary, @password)) , @sees_public)
SELECT ID = @@IDENTITY
__________________________
The error is:
__________________________
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to smalldatetime.
__________________________
In the database - the birthdate field is stored as datetime, 8 chars
Can you help me please?
My output is:
__________________________
execute sp_insertreminderuser 'jo', 'bloggs', '28/10/1965', 'joe@bloggs.com', 'jo', 1
__________________________
The SP is:
__________________________
CREATE PROCEDURE sp_insertreminderuser
@firstname varchar(50),
@lastname varchar(50),
@birthdate smalldatetime,
@email varchar(100),
@password varchar(25),
@sees_public char(1)
AS
INSERT INTO tblusers (firstname, lastname, birthdate, email, password, sees_public)
VALUES (@firstname, @lastname, @birthdate, @email, (convert(varbinary, @password)) , @sees_public)
SELECT ID = @@IDENTITY
__________________________
The error is:
__________________________
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to smalldatetime.
__________________________
In the database - the birthdate field is stored as datetime, 8 chars
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CAST(@birthdate as smalldatetime) and CAST(@birthdate as datetime) don't work where the date is less than 8 chars but I'm wondering if its because not all 8 chars are completed. Example: '1965928' instead of '19650928'
28-Sep-1965 should work.
ASKER
does it have to 19650928 or can it be 1965928 to work?
ohhh.. didn't notice you used smalldatetime in the sp.
you can use the following suggestion:
https://www.experts-exchange.com/questions/21086240/Date-Format-on-Insert-Stored-Procedure.html
or even use CDate in VBScript:
execute sp_insertreminderuser 'jo', 'bloggs', CDate('28/10/1965'), 'joe@bloggs.com', 'jo', 1
you can use the following suggestion:
https://www.experts-exchange.com/questions/21086240/Date-Format-on-Insert-Stored-Procedure.html
or even use CDate in VBScript:
execute sp_insertreminderuser 'jo', 'bloggs', CDate('28/10/1965'), 'joe@bloggs.com', 'jo', 1
Use this at the top of your script:
SET DATEFORMAT dmy
execute sp_insertreminderuser 'jo', 'bloggs', '28/10/1965', 'joe@bloggs.com', 'jo', 1
SET DATEFORMAT dmy
execute sp_insertreminderuser 'jo', 'bloggs', '28/10/1965', 'joe@bloggs.com', 'jo', 1
ASKER
I'm still having problems where the date is YYYYMDD rather than YYYYMMDD
For instance- my SP is
CREATE PROCEDURE sp_insertnewreminder
@userid int,
@name varchar (50),
@occasion int,
@firstdate datetime
AS
INSERT INTO tblreminders (userid, name, remindertypeid, firstdate)
VALUES (@userid, @name, @occasion, CAST(@firstdate as datetime))
SELECT ID = @@IDENTITY
GO
and the firstdate variable in this instance = 2004112 [2 November 2004], if I select [30 November 2004] 20041130, it works OK but otherwise I get:
Error converting data type varchar to datetime.
For instance- my SP is
CREATE PROCEDURE sp_insertnewreminder
@userid int,
@name varchar (50),
@occasion int,
@firstdate datetime
AS
INSERT INTO tblreminders (userid, name, remindertypeid, firstdate)
VALUES (@userid, @name, @occasion, CAST(@firstdate as datetime))
SELECT ID = @@IDENTITY
GO
and the firstdate variable in this instance = 2004112 [2 November 2004], if I select [30 November 2004] 20041130, it works OK but otherwise I get:
Error converting data type varchar to datetime.
Did you try my solution? It's much simpler.
ASKER
my sql to update the sp is
sql = "execute sp_insertnewreminder '" & _
userid & "', '" & _
name & "', '" & _
Occasion & "', '" & _
firstdate & "'"
where would SET DATEFORMAT dmy go? and do I still have to use CAST in the sp?
sql = "execute sp_insertnewreminder '" & _
userid & "', '" & _
name & "', '" & _
Occasion & "', '" & _
firstdate & "'"
where would SET DATEFORMAT dmy go? and do I still have to use CAST in the sp?
sql = "SET DATEFORMAT dmy " & vbcrlf & " execute sp_insertnewreminder '" & _
userid & "', '" & _
name & "', '" & _
Occasion & "', '" & _
firstdate & "'"
You will not have to use CAST in your SP. Just make sure the SP data type is SMALLDATETIME. The date '28/10/1965' will be converted automatically.
userid & "', '" & _
name & "', '" & _
Occasion & "', '" & _
firstdate & "'"
You will not have to use CAST in your SP. Just make sure the SP data type is SMALLDATETIME. The date '28/10/1965' will be converted automatically.
ASKER
what if the format is datetime?
>>what if the format is datetime?
It will still work for dates such as '28/10/1965' (it just adds a 12:00:00 for the time)
I'm afraid I've not tested passing a time as well.
It will still work for dates such as '28/10/1965' (it just adds a 12:00:00 for the time)
I'm afraid I've not tested passing a time as well.
ASKER
execute sp_insertreminderuser 'jo', 'bloggs', '19651028', 'joe@bloggs.com', 'jo', 1
So I guess I'm really asking how I can insert '28/10/1965' as a date?
And how to make sure that all 8 chars are completed as '1965928' doesnt work but 19650928 does?