add time to datetime value and split into date and time

Hi

i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).

could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).

Thanks

V.

at the end i found it myself

this is how i solved it.

to add the timezone value (in minutes) to my datetime i used following :

DATEADD(minute, TimeZone, DATETIMEVALUE) AS ACTUALDATETIME

this expression will add the timezone value (example 60) to my current date time value (12/06/2007 08:00:00) and store it in

actualdatetime value (12/06/2007 09:00:00).

from then on it's easy to extract date and time from the result.

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 103) AS DATEVAL for the DATE

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 108) AS TIMEVAL for the time.

i thank myself for my research .. lol

Greetings to all

Add similar fields to many tables

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @.name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @.name
IF @.@.fetch_status = 0
BEGIN
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_date [DATETIME]
GO
ALTER TABLE @.name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur

I also want that if one column for a table exists; the other columns should be created rather than it quits.

Howdy!Folks, please help!
Or do i have to add the columns manually! :confused:

Howdy!|||Maybe if you are concerned about whether a column already exists it would be easier to run the four different ALTER statements separately rather than coding around possibilities...y'know, four scripts that you run against all the tables instead of one.

Just a suggestion.

But for a one-time modification like this, you have to be careful that you don't spend more time trying to devise the most optimal and elegant solution than it would take you to just knuckle down and do the dirty work.|||declare @.tablename varchar(50)
declare @.add_field varchar(255)

declare cur_tables cursor for
select name from sysobjects where type='u' and status not like '-%'

open cur_tables
fetch next from cur_tables into @.tablename
while @.@.fetch_status = 0
begin
--print 'table name is ' + @.tablename

set @.add_field = 'alter table ' + @.tablename + ' add created_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add created_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

fetch next from cur_tables into @.tablename
end
close cur_tables
deallocate cur_tables|||-- Run this and cut results to query window & run it.
-- Tim S

SET NOCOUNT ON;
SELECT
CASE WHEN col1.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_BY VARCHAR(25);' + CHAR(10) ELSE '' END +
CASE WHEN col2.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_DATE [DATETIME];'+ CHAR(10) ELSE '' END +
CASE WHEN col3.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_BY VARCHAR(25);'+ CHAR(10) ELSE '' END +
CASE WHEN col4.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_DATE [DATETIME];'+ CHAR(10) ELSE '' END
FROM INFORMATION_SCHEMA.TABLES tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col1
ON tab.TABLE_NAME = col1.TABLE_NAME AND tab.TABLE_SCHEMA = col1.TABLE_SCHEMA AND 'CREATED_BY' = col1.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col2
ON tab.TABLE_NAME = col2.TABLE_NAME AND tab.TABLE_SCHEMA = col2.TABLE_SCHEMA AND 'CREATED_DATE' = col2.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col3
ON tab.TABLE_NAME = col3.TABLE_NAME AND tab.TABLE_SCHEMA = col3.TABLE_SCHEMA AND 'MODIFIED_BY' = col3.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col4
ON tab.TABLE_NAME = col4.TABLE_NAME AND tab.TABLE_SCHEMA = col4.TABLE_SCHEMA AND 'MODIFIED_DATE' = col4.COLUMN_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE' AND -- tab.TABLE_NAME = 'test2' AND
( col1.COLUMN_NAME IS NULL OR col2.COLUMN_NAME IS NULL OR col3.COLUMN_NAME IS NULL OR col4.COLUMN_NAME IS NULL)

Add days to all datetime columns in the database

Hi,
I got a request from our Sr. Director regarding demo data.
He's stated that demo databases tend to age with time. As
you enter orders they become older and older as time
passes.
Anyways, he's asking how difficult would it be to write a
SQL utility that asks the user for a number of days and
then ran through the ENTIRE database looking for EVERY
Date/Time column and add the number of days to the data
content of the field.
I've sort of created already somewhat a DML command that
will autogenerate for me the update statement, but is
there a way for me to prompt the user in Query Analyzer
for a value to set the variable? Does it have to be done
via command prompt?
I've copied below my DML statement that I've quickly
written up.
select 'update ' + OBJECT_NAME(id) + ' set ' + substring
(name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
OBJECT_NAME(id)
from syscolumns
where name like '%Date%'
order by OBJECT_NAME(id)
TIA,
BettinaYou can make a template in Query Analyzer ...
You can insert tags like: <myTag, varchar(20), 'abc'> (Structure is:
<TagName, DataType, Default>)
Then just hit CTRL-SHIFT-M and it will prompt for replacement. Very handy.
You can also save your template in the <SQL Server
Root>\80\Tools\Templates\SQL Query Analyzer\ folder, as a .TQL file, and
when you click on the Templates tag in Query Analyzer it will be there for
you to use.
Or you could just make a stored procedure...
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>|||In the databases I see you will pick up lots of things that don't correspond
to an SQL datetime data type.
If you get this syscolumns.xtype contain they system type of the column.
syscolumns.xtype in (58, 61) will pick all datetime columns.
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:051601c3a3f6$91dcb9f0$a401280a@.phx.gbl...
> Hi,
> I got a request from our Sr. Director regarding demo data.
> He's stated that demo databases tend to age with time. As
> you enter orders they become older and older as time
> passes.
> Anyways, he's asking how difficult would it be to write a
> SQL utility that asks the user for a number of days and
> then ran through the ENTIRE database looking for EVERY
> Date/Time column and add the number of days to the data
> content of the field.
> I've sort of created already somewhat a DML command that
> will autogenerate for me the update statement, but is
> there a way for me to prompt the user in Query Analyzer
> for a value to set the variable? Does it have to be done
> via command prompt?
> I've copied below my DML statement that I've quickly
> written up.
> select 'update ' + OBJECT_NAME(id) + ' set ' + substring
> (name,1,30) + ' = ' + name + ' + @.adddays' + ' from ' +
> OBJECT_NAME(id)
> from syscolumns
> where name like '%Date%'
> order by OBJECT_NAME(id)
> TIA,
> Bettina
>
>

Add Date only to Time only = datetime ?

I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
CarlIf I understand you, you want use date from EVT_DT, use time from EVT_TM put them together and retrieve datetime: '2005-12-05 13:59:00'

if I'm right, transform dates to varchar, cut what u need, concatenate strings and transform it back to date time:

declare @.EVT_DT datetime
declare @.EVT_TM datetime

set @.EVT_DT = '2005-12-05 00:00:00'
set @.EVT_TM = '2005-12-06 13:59:00'

select
cast(
cast(@.EVT_DT as varchar(11)) + ' ' +
cast(datepart(hh, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(mi, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(ss, @.EVT_TM) as varchar(2))
as datetime)

2005-12-05 13:59:00.000|||I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
Carl

Just so you are aware, using your examples above, SQL would "treat" EVT_DT as 38689.00000 and EVT_TM as 38690.58264. So, when you try to add them together, you're getting an unexpected value. A better practice would be to store the completed Date/Time together in a single column and then deal with the issue of selecting for a specific data by using BETWEEN.

Regards,

hmscott