Inserting data to column having smalldatetime dataype

Question

Post reply

Inserting data to column having smalldatetime dataype

kanth.vishal

SSC Journeyman

Points: 93
More actions
May 28, 2010 at 11:00 am

#140525

Hello,
I am trying to insert a data into the database column having data type as smalldatetime with null value allowed.
the input data is 19010101 this is the data for date ,when i try to insert this data into a column having datatype as smalldatetime ,what I find is that the inserted value is not the date 19010101 but a null value,how can that be possible

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Because you're trying to do it as an integer...

You need to convert put it in a string to convert it to smalldatetime. Try:

set MyField = convert(char(8), 19000101)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kanth.vishal SSC Journeyman Points: 93 More actions · Answer 2

I have already converted this into string using data transformation column ie DT_STR

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

kanth.vishal (5/28/2010)
I have already converted this into string using data transformation column ie DT_STR

Strange, this works at my system:

declare @test smalldatetime;

select @test = CONVERT(char(8),19010101);

print @test

Is it only for the date 19010101 or for all the dates that you try to insert?

Is everything mapped correctly in the Destination?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP