Help with Date Conversion - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Date Conversion

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-09-11 : 05:41:42

DECLARE @PurchaseYear as Datetime

SET @PurchaseYear = 'SELECT ORDER.ORDERID, YearTable.Year FROM ORDER INNER JOIN Year ON ORDER.ORDERYearPurchase = YearTable.YearID'

INSERT INTO ORDERTable.dbo
(PurchasedYear)
VALUES
(@PurchaseYear)

I have a table called YearTable that has an ID and Year (in the format 1900, 1901, 1902...). This is joined to my main table (ORDER) by ID. What i am attempting to do is get all the Years related by Order ID and insert them into a new column (PurchasedYear)....So far all this works but i get the error "Conversion failed when converting datetime from character string." using the above code. I understand why this is happening (i believe its because its trying to convert 1901 to 1901.12.1 00:00:00:000) so im not sure how i could convert that column into DateTime? Could anyone assist please?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-12 : 03:17:05

INSERT INTO ORDERTable.dbo
(PurchasedYear)
SELECT YearTable.Year FROM ORDER INNER JOIN Year ON ORDER.ORDERYearPurchase = YearTable.YearID

N 56°04'39.26"
E 12°55'05.63"

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-09-13 : 14:14:02

That didnt work and returned the same error ("Conversion failed when converting datetime from character string.")

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 14:38:45

So... What you are telling us is that YearTable.Year column is NOT datetime?
If this is true, why isn't it datetime? Is it varchar? And if so, why!!??!?!

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 14:39:35

Which datetime format are you using for storing date values?
Read Books Online for CAST and how to convert varchar datetime values into correct datetime.

N 56°04'39.26"
E 12°55'05.63"

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-09-13 : 15:15:09

Year is Int - this database was taken many years ago and i need the data to be in the correct format so it would be usable. I read BOL but i couldnt get the examples to work.... doing this a little more i now receive the error "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-13 : 15:16:41

Perhaps you should start with posting your FULL problem query?

N 56°04'39.26"
E 12°55'05.63"

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-09-13 : 15:36:18

For brevity i have 2 tables ORDERS and YEAR.

Order:
OrderID
Name
..
...
YearPurchased (Int - FK)

Year:
YearID
Year

Sample of the Year table data (ID Year):
1 1901
2 1902
3 1903
4 1904

The ID is a PK and is joined with Order YearPurchased...... Now thats wrong so i created another column in the ORDERS table called NewYearPurchased (example). Into this column i am trying to get all the years tied up with the orders to be inserted into this table but in the new column without any joins/relationship to the year table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources