Changing date and keeping time

Hi! I'm using MS SQL server 2000. How can I change date in date field keepin
g
time the same (datetime field)? I'm using SQL Enterprise Manager for that.
Please help with syntax. More thanks, Alar. PS! Can You suggest some book or
other source I can find hints about MS SQL syntax?
> Hi! I'm using MS SQL server 2000. How can I change date in date field
> keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
That wont work, if you edit the date via EM you always will (implicit)
issue a command like:
UPDATE Sometable SET Somecolumn '01/01/2004 00:00:00' Where ...
even if you type in '01/01/2004'. The function DATEADD or if you prior cut
out the time and put it on the changed column (time 00:00:00) would work
fine.
PS! Can You suggest some book or
> other source I can find hints about MS SQL syntax?
Did you try BOL ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||> Hi! I'm using MS SQL server 2000. How can I change date in date field
keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax.
See function CONVERT in BOL.
Example:
update table1
set c1 = '2005-05-09' + right(convert(varchar(25), c1, 126), 13)
where c1 >= '20050501' and c1 < '20050508'
AMB

> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?
MS SQL Server 2000's Books Online
AMB
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||The DateAdd() function can add arbitrary number of days, ws, months, or
whatever, to a given date. If you choose any time increment greater than a
day, the time portion of the value will remain the same...
Otherwise, you need to update the column to a new date with the same time as
the the datetime that's in there...
Update TablleName Set
DTColumn = 'NewDate as CCYYMMDD ' +
convert(VarCHar(12), DTColumn, 14)
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||I don't recommend using Enterprise Manager for this, and
I suggest you run the update query in Query Analyzer
instead.
If @.newDate is the new date, T is your table, myDate is the
column you want to change, and rowKey = @.rowKey identifies
the row you want to change, this will work (not tested - watch
for typos)
update T set
myDate = dateadd(day, datediff(day, myDate, @.newDate), myDate)
where rowKey = @.rowKey
This will add a whole number of days to myDate, the number
it adds being exactly the number of whole days from myDate
to @.newDate.
Steve Kass
Drew University
Alar Pandis wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?