SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 convert varchar to datetime fails on insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elshorty
Starting Member

4 Posts

Posted - 11/28/2012 :  05:03:42  Show Profile  Reply with Quote
I need to insert a varchar in my table. The type in the table is a datetime so I need to convert it. I didn't think this would be to big of a problem however it keeps inserting 1900-01-01 00:00:00.000 instead of the date I want. When I do a select with my converted date it does show me the correct date.

I'll show you the code:


INSERT INTO Item (CategoryId, [Date], Content, CreatedOn)
   SELECT 
       CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
   FROM 
       Item i
   JOIN 
       Category c ON i.CategoryId = c.Id
   JOIN 
       Division d ON d.Id = c.DivisionId
   WHERE 
       Date = Convert(datetime, '31/03/2005', 103) 
       AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'


The where clause works perfectly and gives me the filtered items I need, all data is correctly inserted except for the converted date. The gives like I said 1900-...

If I just do the select so:


SELECT CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
FROM Item i
JOIN Category c ON i.CategoryId = c.Id
JOIN Division d ON d.Id = c.DivisionId
WHERE Date = Convert(datetime, '31/03/2005', 103) AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'


I get the correct date being: 2012-11-28 00:00:00.000. I have tried to use a different conversion like:


Convert(datetime, '20121128')
CAST('20121128' AS DATETIME)


The where clause works perfectly, the select shows me the date I want but on insert it keeps inserting: 1900-01-01 00:00:00.000

Anyone know why it is doing this? Any ideas or help would greatly be appreciated

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 11/28/2012 :  05:30:31  Show Profile  Visit webfred's Homepage  Reply with Quote
What is the datatype of Item.[Date]?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

elshorty
Starting Member

4 Posts

Posted - 11/28/2012 :  05:32:18  Show Profile  Reply with Quote
it is a datetime.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 11/28/2012 :  05:34:13  Show Profile  Visit webfred's Homepage  Reply with Quote
Is there a trigger involved?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

elshorty
Starting Member

4 Posts

Posted - 11/28/2012 :  05:48:08  Show Profile  Reply with Quote
No, there is no trigger
Go to Top of Page

elshorty
Starting Member

4 Posts

Posted - 11/28/2012 :  08:28:56  Show Profile  Reply with Quote
Nvm, I just found my mistake, in the full query (to big to post here so I took some items out) I accidently switched to rows from place ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000