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 3 (of 3 total)

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

WayneS SSC Guru Points: 95460 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: 259105 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-2 smalldatetime;

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

print @test-2

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