converting nvarchar to smalldatetime

[Lalit singh]

Hi Thanks For your reply , The thing is that i have one table in which date is stored in column(datataype nvarchar) There are more than lacs of records in this table. so i want to convert this field into following format ORIGINAL FORMAT OF FIELD IN TABLE (DATATYPE:-NVARCHAR) => 30/11/2009 I WANT INTO FORMAT : 30112009 AND ANOTHE FORMAT : 30/11/2009(SMALL DATETIME) Please help me out. I tried the solution which you gave but its giving following error "Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type smalldatetime. " Please help me out

[Luc Pattyn]

why would you choose smalldatetime when all you want to store is a date? :)

Luc Pattyn

---

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

---

Local announcement (Antwerp region): Lange Wapper? 59.24% waren verstandig genoeg om NEEN te stemmen; bye bye viaduct.

---

[Lost User]

Questions such as this explain so much about the world we now find ourselves in :laugh:

[Ashfield]

Not sure why you get this error.

declare @a nvarchar(20)
select @a = '30/11/2009'
select convert(smalldatetime,@a)

gives me 2009-11-30 00:00:00, which is correct. How it is displayed (i.e. 30/11/2009 or 30112009) is down to your format mask, SQL Server simply stores the datetime value as a number. Please, before posting any more questions around this, use the F1 key and read up on date time data types.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Mycroft Holmes]

From the discussion I gather that you are running up against the first problem of storing date data in a non date data format. You have precicely 1 choice to fix this problem, change your data type to datetime NOW, if you use a work around to convert from nvarchar to datetime then you will always be performing that work around. STOP AND FIX YOUR DATA. I suggest creating a new column in a datetime format and convert/copy the data to that field. You are already getting an error (arithmetic) so I know your nvarchar data is corrupt. I would recursively convert as much as possible and then manually update the rest.

[Lalit singh]

What you said is very correct , i am not denying it. The thing is that i am inserting into destination column(smalldatetime) from source column(nvarchar) and the number of records are around 4lac. I am inserting through sql script in query analyser. After insrting some 20k records it generates that "Arithmetic overflow error". This was task given to me. thats why otherwise i could change my destination column to datetime. Its working. But I am forcebly want it to smalldatetime as this was task for me.

[_Damian S_]

Have you done any data analysis to make sure that all the values you are trying to convert fall into the acceptable range for smalldatetime values? Perhaps you have a record (around the 20K mark) that has a typo in the date, and instead of 30/11/2009 it's 31/11/2009 (which isn't valid) or 30/11/9999...

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[Ashfield]

So you have an invalid date in there. Use the isdate function to find any that are invalid.

declare @a nvarchar(20)
set @a = '30/11/2009'
select isdate(@a)
-- returns 1
set @a = '31/11/2009'
select isdate(@a)
-- returns 0

Its not rocket science.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Lalit singh]

You were correct there were dates in dd/mm/yyyy which were incorrect format according to ISDATE() function . So i converted it into mm/dd/yyyy format and it worked. Now thing is that i just saved in date format only like '11/30/2009' but in table having column date (datatype smalldatetime) it stored like '11/30/2009 00:00:00.000' I want to store only date (datatype=smalldatetime). Is there any way to store into sql server 2005 date column with date only excluding time.

[Ashfield]

Lalit singh wrote:

Is there any way to store into sql server 2005 date column with date only excluding time.

No, as I said in an earlier post, sql server stores the date and time as a number, not in any format. Just use a format when you display it.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[Lalit singh]

Thank you very much for your valuable response.