Concatenation of Date ant Time

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

General SQL Server Forums

New to SQL Server Programming

Concatenation of Date ant Time

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-07-01 : 01:28:03

Hi Friends

I had table which has Four fields where date and Time is stored in seperate fields

Table Fields
1.UId
2.Name
3.Date
4.Time

Here i need to concatenate the date and time and compare with current date and time minus 2 hours(i,e if current date and time is 07/01/2009 10:53:000 ,we need to take it as 07/01/2009 8:53:000) and select less than 07/01/2009 8:53:00 values from the table.

If you need to be explained more, i will explain.

Please help me regarding the query.

Thanks in Advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 01:51:20

i assume your Time is a datetime data type ?


select *
from   table
where  [Date] <= dateadd(day, datediff(day, 0, getdate()), 0)
and    [Time] <= dateadd(hour, -2, getdate()) - dateadd(day, datediff(day, 0, getdate()), 0)

KH
[spoiler]Time is always against us[/spoiler]

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-07-01 : 01:57:55

Try this

SELECT DATEADD(hh,-2,'07/01/2009 10:53:000')

Kotti
Posting Yak Master

129 Posts

Posted - 2009-07-01 : 03:20:15

Hi Friends

Thank you for your Reply.

Here my Time field is varchar so how can i modify this?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 03:23:08

quote:
Originally posted by Kotti

Hi Friends

Thank you for your Reply.

Here my Time field is varchar so how can i modify this?

Thanks

how is the time format like in your varchar field ? does it prefix with 0 for hour less than 10 ? like 03:45:12 ?

KH
[spoiler]Time is always against us[/spoiler]

Kotti
Posting Yak Master

129 Posts

Posted - 2009-07-01 : 03:30:15

I have the value for time field has

Time
10:30AM
1:00PM
3:00AM

Like that i have my fields

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 03:33:23

[code]
select *
from table
where [Date] <= dateadd(day, datediff(day, 0, getdate()), 0)
and convert(datetime, [Time]) <= dateadd(hour, -2, getdate()) - dateadd(day, datediff(day, 0, getdate()), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Kotti
Posting Yak Master

129 Posts

Posted - 2009-07-01 : 07:26:12

Hi Friend,

Thanks

I am geting error when i use this
convert(datetime, [Time])

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 07:29:04

And it is not possible to post the error?
...getting error... is always not helpful for those who want to solve YOUR problems!

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Kotti
Posting Yak Master

129 Posts

Posted - 2009-07-01 : 07:42:28

Friend,
Sorry for this ,i just tried in another System

I am Getting Error as
'Conversion failed when converting datetime from character string.’

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 08:55:43

it must be the data. Check the value in your Time column see any abnormal value

or try this


select *
from   table
where  isdate(Time) <> 1

KH
[spoiler]Time is always against us[/spoiler]

Kotti
Posting Yak Master

129 Posts

Posted - 2009-07-01 : 10:17:16

Hi Friends

Thank you for all your helps

One of the value in the time field was wrong
Now i am getting the answer.

Your help will be much appreciated.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources