replcae date iwth other date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

replcae date iwth other date

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-20 : 03:17:55

Dear All,

I am having two date column for swipe entry

Table name is iotrans

column-dt,intime,outtime

it will give the swipein details of all employee

look the data below

Swipe date intime outtime
2012-06-20 00:00:00.000 1900-01-01 11:37:00.000 1900-01-01 12:30:00.000 2012-06-19 00:00:00.000 1900-01-01 11:08:00.000 1900-01-01 20:31:00.000
2012-06-18 00:00:00.000 1900-01-01 10:37:00.000 1900-01-01 20:45:00.000
2012-06-12 00:00:00.000 1900-01-01 10:54:00.000 1900-01-01 20:11:00.000
2012-06-11 00:00:00.000 1900-01-01 10:59:00.000 1900-01-01 20:48:00.000
2012-06-08 00:00:00.000 1900-01-01 11:23:00.000 1900-01-01 20:37:00.000
2012-06-07 00:00:00.000 1900-01-01 11:43:00.000 1900-01-01 20:42:00.000
2012-06-06 00:00:00.000 1900-01-01 15:08:00.000 1900-01-01 20:39:00.000
2012-06-05 00:00:00.000 1900-01-01 12:07:00.000 1900-01-01 21:26:00.000
2012-06-04 00:00:00.000 1900-01-01 11:25:00.000 1900-01-01 20:28:00.000

if you see the data you will get swipedate,intime,outtime

in intime as you can see time is there but date is 1900-01-01 which i dont want

i want to have intime like 2012-06-20 11:37:00.000 (intime + time)

and outtime lik 2012-06-20 12:30:00.000(outime+date)

my query is

select top 10 dt,intime,outtime from IOTRANS

is there any replace function we can used for this ?

regards,

Vipin jha

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-20 : 08:05:11

Is this what you're looking for?

declare @date1 datetime = '2012-06-20 00:00:00.000'
,@date2 datetime = '1900-01-01 11:37:00.000'

SELECT DATEADD(day,datediff(day,@date2,@date1),@date2)

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-20 : 08:49:03

If all default to date 0, you can do dateintime+dateouttime

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources