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
 General SQL Server Forums
 New to SQL Server Programming
 Datetime - how to add one to existing table

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 3613
I wrote ...

UPDATE ImportData
Set 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 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';
Go to Top of Page

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-24 : 20:58:27
It works perfectly, thank you.
Go to Top of Page

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-24 : 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.
Go to Top of Page

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.
Go to Top of Page

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 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

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"
Go to Top of Page

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.

Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -