| Author |
 Topic  |
|
|
ktkn
Starting Member
13 Posts |
 Posted - 12/06/2012 : 17:28:23
|
from exting table, i have 2 fields for date and time modified. I need to create the new table with only one field for DateTimeModified and put those two value into this new field. Can
you show me how?
tbl1
date_modified time_modified
2002-11-11 00:00:00.000 165628
2002-11-11 00:00:00.000 165638
tb2
DateTimeModified
query:
insert into tb2
set DateTimeModified =select (date_modified+time_modified) as DateTimeModified
?
Thanks |
|
|
nelsont
Starting Member
USA
19 Posts |
Posted - 12/06/2012 : 17:39:34
|
You need to cast them both as datetime. Set <destination_field> = cast(date_field as datetime) + cast(time_field as datetime)
|
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 12/06/2012 : 18:00:59
|
Is the data type of 165628? Is it a string? If so is it always 6 characters long? Or is it something else?
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 12/06/2012 : 18:08:25
|
Without knowing the details, Here is a guess:SELECT
CAST(LEFT(date_modified, 11) + STUFF(STUFF(time_modified, 3, 0, ':'), 6, 0, ':') AS DATETIME)
FROM
tbl |
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 12/07/2012 : 09:05:06
|
Lamprey,
the data type of 165628 is int, then what should i do?
Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 12/07/2012 : 09:10:23
|
quote:
Originally posted by ktkn
Lamprey,
the data type of 165628 is int, then what should i do?
Thanks
what does it represent?
does it represent time of 16:56:28 or is it 165628 seconds ?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 12/07/2012 : 09:17:37
|
| i just shows: 165628 |
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 12/07/2012 : 09:31:28
|
i had tried Lamprey's code but got an error:
Conversion failed when converting datetime from character string..
old values
date_modified time_modified
2004-09-03 00:00:00.000 222620
new with the qurey after Concatenate
select cast(date_modifiedas datetime) + cast(time_modified
as datetime)
2614-03-10 00:00:00.000 |
Edited by - ktkn on 12/07/2012 09:37:14 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 12/07/2012 : 09:36:48
|
quote:
Originally posted by ktkn
i just shows: 165628
I was asking what it represents
DO you mean you dont know whether above means 16:56:28 or just an int value?
I guess it should be former though!
also how are date values present in date_modified? is it always in same format or is it of datetime type?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 12/07/2012 : 09:40:26
|
| what I see is the data type for time_modified is int, it has diffrent time on it. i see some have 5 number, 6 number, etc. The data type for date_modified is datetime. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 12/07/2012 : 10:17:50
|
select dateadd(ss,time_modified %100,dateadd(n,(time_modified %10000)/100,dateadd(hh,time_modified/10000,date_modified ))) from table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 12/07/2012 : 10:19:18
|
Working with the assumption that time_modified represents the time as an integer in format HHMMSS and the data is valid, this will work:
select
a.*,
time_datetime =
stuff(stuff(right('000000'+convert(varchar(10),time_modified),6),5,0,':'),3,0,':'),
Combined_Datetime = date_modified+
case
when -- test for invalid time
time_modified between 0 and 235959 and
isDate(stuff(stuff(right('000000'+convert(varchar(10),time_modified),6),5,0,':'),3,0,':')) = 1
then
stuff(stuff(right('000000'+convert(varchar(10),time_modified),6),5,0,':'),3,0,':')
end
from
( --Test Date
select date_modified = convert(datetime,'20040903'),
time_modified = 165628
union all
select date_modified = convert(datetime,'20040904'),
time_modified = 000201
union all
select date_modified = convert(datetime,'20040905'),
time_modified = 009901
union all
select date_modified = convert(datetime,'20040906'),
time_modified = 1245628
) a
order by
a.date_modified,
a.time_modified
Results:
date_modified time_modified time_datetime Combined_Datetime
----------------------- ------------- ------------- -----------------------
2004-09-03 00:00:00.000 165628 16:56:28 2004-09-03 16:56:28.000
2004-09-04 00:00:00.000 201 00:02:01 2004-09-04 00:02:01.000
2004-09-05 00:00:00.000 9901 00:99:01 NULL
2004-09-06 00:00:00.000 1245628 24:56:28 NULL |
Edited by - Michael Valentine Jones on 12/07/2012 10:29:29 |
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 12/07/2012 : 11:06:50
|
| it worked, thank you |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 12/07/2012 : 11:24:28
|
quote:
Originally posted by ktkn
it worked, thank you
Which one worked?
CODO ERGO SUM |
|
|
| |
Topic |
|
date/time concatenate
