Author |
Topic |
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-06-24 : 20:19:40
|
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 3613I wrote ...UPDATE ImportDataSet Date = '14/06/2009 10:33:32'Where Group = '3613' It does not work |
|
eonmantra
Starting Member
11 Posts |
Posted - 2009-06-24 : 20:40:09
|
Try this:UPDATE ImportDataSet 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 ImportDataSet Date = CONVERT(DATETIME, '14/06/2009 10:33:32', 103)Where [Group] = '3613'; |
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-06-24 : 20:58:27
|
It works perfectly, thank you. |
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-06-24 : 21:08:58
|
UPDATE ImportDataSet 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. |
|
|
eonmantra
Starting Member
11 Posts |
Posted - 2009-06-24 : 21:41:16
|
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. |
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-06-24 : 22:00:14
|
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 ImportedSquadSet Date = (SELECT CONVERT(DATETIME, (SELECT tab.col.value('./date[1]','varchar(19)') AS 'Date'FROM [ImportedXML]CROSS APPLYxml_data.nodes('//request') AS tab(col)), 103) AS Date) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-25 : 00:54:12
|
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" |
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-06-25 : 01:55:09
|
@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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-25 : 02:40:20
|
The dateformat in XML files often includes a 'T' character, according to ISO8601. E 12°55'05.63"N 56°04'39.26" |
|
|
|