| Author |
 Topic  |
|
|
Mopani
Yak Posting Veteran
55 Posts |
 Posted - 06/24/2009 : 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 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
|
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 |
 |
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 06/24/2009 : 20:58:27
|
It works perfectly, thank you.
|
|
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 06/24/2009 : 21:08:58
|
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. |
|
|
|
eonmantra
Starting Member
USA
11 Posts |
Posted - 06/24/2009 : 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 - 06/24/2009 : 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 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)
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 06/25/2009 : 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 - 06/25/2009 : 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
Sweden
29156 Posts |
Posted - 06/25/2009 : 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" |
|
|
| |
Topic |
|
Datetime - how to add one to existing table
