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]

Re: Removing 1 hour from text field

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(DATETIM E, 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(DATETIM E, 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]

Re: Removing 1 hour from text field

On 5 Mar, 14:39, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

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]

Re: Removing 1 hour from text field

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]

Re: Removing 1 hour from text field

maury (maurizio.alber ti@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Marc]

Re: Removing 1 hour from text field

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" <maurizio.alber ti@gmail.comwro te in message
news:c62d8965-5e8a-4328-af10-60690ab1090e@s1 3g2000prd.googl egroups.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