Removing 1 hour from text field

[maury]

Hello,
I have an SQL Server table with a (text) field in which there is
a data value in this format:
200802290525

I need to build an update query the modifies this value
subtracting 1 (one) hour from this value (if the hour is
00:30 am the day is to be decreased and so on)

How can I sipmly accomplish to this job?

Thanks!!!

[Plamen Ratchev]

The problem just illustrates why dates should not be kept as text strings.
Correct calculations with dates can be done easily only when the data type
is DATETIME/SMALLDATETIME.

Here is one way to do the 1 hour subtraction:

SELECT DATEADD(hh,
- 1,
LEFT(d, 8) +
CONVERT(DATETIME, LEFT(RIGHT(d, 4), 2) + ':' +
RIGHT(RIGHT(d, 4), 2),
108))
FROM (SELECT '200802290525' UNION
SELECT '200802290030') AS T(d)

And then it gets even more messy converting back to string format:

SELECT REPLACE(
REPLACE(
REPLACE(
CONVERT(VARCHAR(16),
DATEADD(hh,
- 1,
LEFT(d, 8) +
CONVERT(DATETIME, LEFT(RIGHT(d, 4), 2) + ':' +
RIGHT(RIGHT(d, 4), 2),
108)),
126),
'-', ''),
'T', ''),
':', '')
FROM (SELECT '200802290525' UNION
SELECT '200802290030') AS T(d)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[maury]

On 5 Mar, 14:39, "Plamen Ratchev" <[e-mail]:

The problem just illustrates why dates should not be kept as text strings.

Yes, I know but the problem is that data are "bulk inserted" from text
files
and we have (several) thousand of lines to import every day, but we
have to shift some old data
back of an hour: is a single step operation...
What about building a custom function or a CLR function to accomplish
this job? Do you think it's a good way to proceed?

Thank for your interest and compliments for your site.. I like it...
Maury

[Plamen Ratchev]

In my opinion it is well worth to explore and test a CLR solution. T-SQL is
not best with string manipulations and CLR can give you good performance,
plus the more powerful .NET formatting functions will make it easier.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

maury (ma**************@gmail.com) writes:

What about building a custom function or a CLR function to accomplish
this job? Do you think it's a good way to proceed?

Yes, putting the logic to handle this string in a CLR function is a
good idea. That's exactly the thing that the CLR is good for: complex
operations without data access. The CLR function is likely to perform
a lot better than Plamen's T-SQL expression.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Marc]

Try this in a query ;) ... see MSDN docs to verify:

declare @mydate datetime

set @mydate = cast('3/25/2008' as datetime)

print DATEADD(hour, -1, @mydate)

print DATEADD(second, -3821, @mydate)

Try and add some time to your date too... and change hour to day or day to
second or second to minute or minute to month and see what happens.

:)

Marc Noon

"maury" <ma**************@gmail.comwrote in message
news:c6**********************************@s13g2000 prd.googlegroups.com...

Hello,
I have an SQL Server table with a (text) field in which there is
a data value in this format:
200802290525

I need to build an update query the modifies this value
subtracting 1 (one) hour from this value (if the hour is
00:30 am the day is to be decreased and so on)

How can I sipmly accomplish to this job?

Thanks!!!

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDem