Date Help

I have a table that contains 5342 records where it is titled:
effbegdat. The table information is 12/30/1899 08:00:00
The date information is all the same, but times are different. I am
attempting to only alter the date. Or update the dates and leave the times
as they are set in the table. This is the formula I am using and it is not
working. I have skimmed through countless sql help sites and can't find a
fix. Any help would be appreciated. Here is my code:
update relent
set effbegdat = datepart(yy,1970) + datepart(m, 1) + datepart(d, 1) where
effbegdat like '%1899%'
Message posted via http://www.webservertalk.comNot sure why you are using the datepart on the SET part, just set the
value like below, you also might want to do the update WHERE the
datepart is 1899, hope this helps.
update relent
set effbeddat = CAST('1/1/1970 AS DATETIME)
where datepart(yyyy, effbegdat) = 1899|||I assume that this is a DATETIME column. Try this:
UPDATE effbegdat
SET dt = DATEADD(D,25569,dt)
WHERE dt >= '18991230'
AND dt < '18991231'
David Portas
SQL Server MVP
--|||I don't think you can use datepart directly on 'SET' . you'll have to use it
in the where clause.
"David Portas" wrote:

> I assume that this is a DATETIME column. Try this:
> UPDATE effbegdat
> SET dt = DATEADD(D,25569,dt)
> WHERE dt >= '18991230'
> AND dt < '18991231'
> --
> David Portas
> SQL Server MVP
> --
>
>|||That changed it, but it also changed the time portion and they want the
time in all records to remain as inputted by the users. I know how to
change a normal column, but just not parts in a column. This column header
is:
effbegdat
--
12/30/1899 08:00:00
12/30/1899 09:00:00
12/30/1899 07:00:10
And so on. They just want the date portion changed and the times to be left
alone. The 2 postings placed in response didn't work. They both changed the
time portions of the column.
Message posted via http://www.webservertalk.com|||I understand what you put in the system. What does this portion mean or
equate to:
(D,25569,dt)
I know the D stands for day and the dt is the variable but what is the
25569?
Message posted via http://www.webservertalk.com|||You can assign any valid expression to a column with UPDATE... SET. Try it
out:
CREATE TABLE effbegdat (dt DATETIME PRIMARY KEY)
INSERT INTO effbegdat
SELECT '1899-12-30T08:00:00'
UPDATE effbegdat
SET dt = DATEADD(D,25569,dt)
WHERE dt >= '18991230'
AND dt < '18991231'
SELECT dt FROM effbegdat
Result:
(1 row(s) affected)
(1 row(s) affected)
dt
--
1970-01-01 08:00:00.000
(1 row(s) affected)
David Portas
SQL Server MVP
--|||25569 is the difference in days between 1899-12-30 and 1970-01-01. DATEADD
just adds on that number of days. The time part is ignored and will not be
changed (assuming I was right and the column is in fact a DATETIME).
David Portas
SQL Server MVP
--|||The dates need an adjustment of datediff(day,effbegdat,'19700101'):
update relent set
effbegdat = effbegdat + datediff(day,effbegdat,'19700101')
where effbegdat >= '18991230'
and effbegdat < '18991231'
This is identical to David's suggestion, but you don't
have to wonder what 25569 is for.
You could also do this, if you want more things to wonder
about!
update relent set
effbegdat = effbegdat + 25569
where effbegdat >= -2
and effbegdat < -1
Steve Kass
Drew University
tina via webservertalk.com wrote:

>I have a table that contains 5342 records where it is titled:
>effbegdat. The table information is 12/30/1899 08:00:00
>The date information is all the same, but times are different. I am
>attempting to only alter the date. Or update the dates and leave the times
>as they are set in the table. This is the formula I am using and it is not
>working. I have skimmed through countless sql help sites and can't find a
>fix. Any help would be appreciated. Here is my code:
>update relent
>set effbegdat = datepart(yy,1970) + datepart(m, 1) + datepart(d, 1) where
>effbegdat like '%1899%'
>
>|||tina
CREATE TABLE #Test
(
col DATETIME NOT NULL
)
INSERT INTO #Test VALUES ('2005-02-03 08:04:10')
INSERT INTO #Test VALUES ('2005-02-03 09:10:20')
INSERT INTO #Test VALUES ('2005-02-03 15:25:30')
UPDATE #Test SET col=CAST('20050225'AS DATETIME)+CONVERT(CHAR(10),col,108)
DROP TABLE #Test
"tina via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:8d994f2203b1490eb08bc98663ff5b4e@.SQ
webservertalk.com...
> I have a table that contains 5342 records where it is titled:
> effbegdat. The table information is 12/30/1899 08:00:00
> The date information is all the same, but times are different. I am
> attempting to only alter the date. Or update the dates and leave the times
> as they are set in the table. This is the formula I am using and it is not
> working. I have skimmed through countless sql help sites and can't find a
> fix. Any help would be appreciated. Here is my code:
> update relent
> set effbegdat = datepart(yy,1970) + datepart(m, 1) + datepart(d, 1) where
> effbegdat like '%1899%'
> --
> Message posted via http://www.webservertalk.com