SmallDateTime SQL DataType - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SmallDateTime SQL DataType

Author

Topic

magnetica
Starting Member

36 Posts

Posted - 2006-09-05 : 17:46:39

Hi im having trouble with SQL datatypes. i am trying to insert into a cell a date, month and day are retrieved from a drop down box whereas the year is retrieved from a textbox.

Also SQL datatypes only allow either 'datetime' and 'smalldatetime'. I am using SmallDateTime (i dont understand why sql doesn't have just a 'date' type)

Either way when i use the below code it inserts the month and day but when it comes to the year, it will always insert either 2001 or 2002 even if thats not what i put in the text box.

'VARIABLES
Dim month As String = DropDownList1.SelectedIndex + 1
Dim day As String = DropDownList2.SelectedItem.Text.ToString()
Dim year As String = dobTextBox.Text.ToString()

'PARAMETER
cmd.Parameters.Add("@dob", SqlDbType.SmallDateTime).Value = month + "/" + day + "/" + year

Anyone understand why it is doing this?

If you think you know it all?! You know nothing!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-05 : 20:10:43

First of all I would replace the parameter code with something that creates the date properly. I think .NET has a Date.Parse function that can help with this.
I would also set a break point directly after populating that parameter and checking the value of the parameter because it will have already parsed it as a date. That might give you some clues.
If not, please post the contents of the stored proc you're trying to run.

HTH,

Tim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 20:54:26

The DateTime datatype in .net has a constructor that accepts a year,month and day:

cmd.Parameters.Add("@dob", SqlDbType.SmallDateTime).Value = new DateTime(Cint(Year), Cint(Month), Cint(Day))

Always use the correct datatypes; don't just stuff everything in Strings.

- Jeff

magnetica
Starting Member

36 Posts

Posted - 2006-09-05 : 21:32:21

Ok thank you! I have got it working.. my column type of DOB is set to smalldatetime and so is the parameter i declare in VB but my stored procedure will not allow me to set the type of @dob to smalldatetime.. if i do i get an error saying:-

"Conversion failed when converting character string to smalldatetime data type."

I have to use nvarchar, is this good or is there another way round this?

'STORED PROCEDURE
ALTER PROCEDURE [dbo].[cprofile]
(@fname nvarchar(50),
@lname nvarchar(50),
@dob nvarchar(MAX),
@gender nvarchar(6),
@about nvarchar(200),
@hobbies nvarchar(200),
@job nvarchar(60)
)

AS
Declare @sql nvarchar(1000)

SELECT @sql = 'INSERT INTO Profile
VALUES (''' + @fname + ''', ''' + @lname + ''', ''' + @dob + ''',
''' + @gender + ''', ''' + @about + ''', ''' + @hobbies + ''',
''' + @job + ''')'

EXECUTE (@sql)

'VB.NET PARAMETER
cmd.Parameters.Add("@dob", SqlDbType.SmallDateTime).Value = New Date(CInt(year), CInt(month), CInt(day))

I should be happy that its working but this is not good and sure it may occur in some error later on..

RETURN

If you think you know it all?! You know nothing!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 21:38:10

YOu do not need dynamic SQL in your stored procedure! And, again, use the proper datatypes !

All you need is:

ALTER PROCEDURE [dbo].[cprofile]
(@fname nvarchar(50),
@lname nvarchar(50),
@dob smalldatetime,
@gender nvarchar(6),
@about nvarchar(200),
@hobbies nvarchar(200),
@job nvarchar(60)
)

AS
INSERT INTO Profile
VALUES (@fname, @lname, @dob, @gender, @about, @hobbies, @job)

Also: list out the columns you are inserting INTO; your code is clearer and easier to troubleshoot and if things ever change you will not have any problems.

Be sure that the DOB column is a datetime and not a varchar or nvarchar in your table as well, of course.

- Jeff

magnetica
Starting Member

36 Posts

Posted - 2006-09-06 : 07:39:35

Thank you! This is very helpful, oh and i am pretty much new to SQL so i am learning!

Cheers for the help

If you think you know it all?! You know nothing!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources