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
 Datetime - how to add one to existing table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mopani
Yak Posting Veteran

55 Posts

Posted - 06/24/2009 :  20:19:40  Show Profile  Reply with Quote
I have existing table with an empty datetime field.
I wish to add the same date and time to every record.
All the records in the table belong to a group called 3613
I wrote ...

UPDATE ImportData
Set Date = '14/06/2009 10:33:32'
Where Group = '3613'

It does not work

eonmantra
Starting Member

USA
11 Posts

Posted - 06/24/2009 :  20:40:09  Show Profile  Reply with Quote
Try this:

UPDATE ImportData
Set Date =  '20090614 10:33:32'
Where [Group] = '3613';


"Group" is a keyword in SQL so you have to wrap it in brackets. I also changed the order of the date to a language neutral format.


Or if you want to keep your date format you'll need to use this code:

UPDATE ImportData
Set Date =  CONVERT(DATETIME, '14/06/2009 10:33:32', 103)
Where [Group] = '3613';

Edited by - eonmantra on 06/24/2009 20:49:15
Go to Top of Page

Mopani
Yak Posting Veteran

55 Posts

Posted - 06/24/2009 :  20:58:27  Show Profile  Reply with Quote
It works perfectly, thank you.
Go to Top of Page

Mopani
Yak Posting Veteran

55 Posts

Posted - 06/24/2009 :  21:08:58  Show Profile  Reply with Quote
UPDATE ImportData
Set Date = CONVERT(DATETIME, '14/06/2009 10:33:32', 103)
Where [Group] = '3613';

I do not know the datetime value that needs to be entered. It needs to be looked up.
This example has a fixed value = '14/06/2009 10:33:32'

Is it possible to place a Select statement in here? I try but get syntax problem and do not know if it's from me being stupid or if it's not possible.
Go to Top of Page

eonmantra
Starting Member

USA
11 Posts

Posted - 06/24/2009 :  21:41:16  Show Profile  Reply with Quote
Show me the select statement you are trying to use and the output it gives you by itself, so I can see what you are working with.
Go to Top of Page

Mopani
Yak Posting Veteran

55 Posts

Posted - 06/24/2009 :  22:00:14  Show Profile  Reply with Quote
oh man, I fixed it. I am learning well.
(bear in mind that the earlier table and column names I gave you were incorrect only samples - I also do not need a WHERE clause since I want all this data to be timestamped.)


UPDATE ImportedSquad
Set Date =  (SELECT CONVERT(DATETIME, (SELECT 
tab.col.value('./date[1]','varchar(19)') AS 'Date'

FROM [ImportedXML]
CROSS APPLY
xml_data.nodes('//request') AS tab(col)), 103) AS Date)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 06/25/2009 :  00:54:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It would be really nice if you have mentioned you are updating XML data, in your original post!



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Mopani
Yak Posting Veteran

55 Posts

Posted - 06/25/2009 :  01:55:09  Show Profile  Reply with Quote
@Peso: Does it really matter that much?
Firstly, I was trying to learn a concept of updating a datetime field. Not looking for a pasted answer.
Secondly, I was not updating XML. I was looking to update my SQL table, which just happens to contain data which I previously pulled out of XML.
How would knowing that, have made any difference to the correct answer?
A datetime field is not something that is exclusive to XML data. The same problem could present itself just because I live in South Africa and use different date formats.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 06/25/2009 :  02:40:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The dateformat in XML files often includes a 'T' character, according to ISO8601.


E 12°55'05.63"
N 56°04'39.26"
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