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.
| Author |
Topic |
|
patjuh112
Starting Member
3 Posts |
 Posted - 2011-05-05 : 05:51:50
|
| Hi,I'm pretty new with SQL (learning, learning) but am trying to get a date field filled with the value from a textfield column that holds a "dd-mm-yyyy" field from the same table. The update query i used and expected to fail is this one : INSERT INTO [Intermediate invoices] (Date)SELECT DISTINCT [Description 7] FROM [Intermediate invoices]The return from SQL is : Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime.Now i do understand that its not accepting it due to it being a date field but after looking into the msdn on cast and convert i'm puzzled on how to approach this, basically i am just trying to literally get the value moved from the text column to the date column since its in the desired format, i want to keep it as dd-mm-yyyyAny ideas or pointers ? |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-05 : 05:55:05
|
| SET DATEFORMAT dmySELECT CAST('25-12-2011' AS DATETIME) |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-05 : 06:01:42
|
1.In CONVERT() the style for dd-mm-yyyy is 105; look here: http://msdn.microsoft.com/de-de/library/ms187928.aspx2. In a column of datatype datetime you can't keep your desired (display-)format. It is an internal format of sql server.Just do your wanted format when displaying the date.3. The solution to your problem above:insert into ... select distinct convert(datetime,[Description 7],105) from [Intermediate invoices]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
patjuh112
Starting Member
3 Posts |
Posted - 2011-05-05 : 06:54:01
|
| Thanks for all the replies, in particular the one from you webfred. I allready was on that msdn page that also described the 105 format but was missing how to place it, not only solved my problem but actually learned from it !/Appreciated ! |
|
|
|
patjuh112
Starting Member
3 Posts |
Posted - 2011-05-05 : 08:09:29
|
| Hmm, something odd is going on. My table for the invoices consists of a rough 9.8k records of which 300 orso have empty fields on description 7 or 8. After running the query it updates 428 records but i'm unable to see why its not picking them all up. The data within (e.g.) description 7 matches the dd-mm-yyyy format and is filled in but for some reason not picked up by this query. I tried several things so far including re-running everything up to the point of this query but i still cant figure out why it does not pick them all up :/After checking what it actually did update (the 428 records) i noticed they got inserted as new records within the table so the only data they have is the date / due date fields filled in from a description 7 and 8 but where from or which lines i cant tell. Like i said i'm pretty new to it but it seems the insert into is something i am messing up since its not updating but truly inserting as new data within the table, the whole command i used is : INSERT INTO [Intermediate invoices] (Date)Select distinct convert(datetime,[Description 7],105) from [Intermediate invoices]Is the insert as i do it there correctly ? I cant see anything wrong with it but figured i might as well check here..Tnx so far |
|
|
|
|
|
|
|
|
Cast and Convert ?