Sql server 2005 - Help with concatenating date and times

[rw409168]

Greetings,

I have an existing database for a time management system with fields as follows:-

bookDate, StartTime, EndTime ALL of type datetime.

Frustratingly there is no datatype 'time' in sql server 2005.

I would like to say in T-SQL Select * from bookings b where (b.bookdate, ddmmyyyy) + (b.startTime, HH:mm) > GETDATE

Or to simplify where the date of the bookdate + the time of the startime together to form a new datetime and then select where it is greater than the current date and time.

I'm working in vb.net and my t-sql knowledge is basic.

I've working with DATEPART and DATAADD functions without success.

Can anyone suggest a way forward?

Thanks
Rob

 

[markros]

Check

http://social.msdn.microsoft.com/Fo...l/thread/19fca846-e397-4d0a-aaeb-c53da82b4c62

- looks like you may need to convert to character first.

 

[simian336]

What are the field types you have currently?

And you might want to provide a data sample.

Simi

 

[rw409168]

All field types are datetime

Example data would be:-

BookDate = 26/11/2009 00:00:00
StartTime = 01/01/1900 10:30:00
Endtime = 01/01/1900 11:00:00

So for sql I would want along the lines of

Select b.*
from bookings b
Where b.bookdate (e.g. '26/11/2009 10:30') > GETDATE

Using convert to change value to char I see nothing for formatting of 24hrs to get HH:mm so looks like I would have to choose one code like 114 then trim some off.

Rob

 

[rw409168]

This looks like a solution:-

select CAST(CONVERT(VARCHAR(8),b.bookdate,112) + ' ' + CONVERT(VARCHAR(8),b.starttime,108) as datetime)
from bookings b

 

[bborissov]

DECLARE @m1 datetime
SET @m1 = '20090101 23:00:00'
DECLARE @m2 datetime
SET @m2 = '20090701 17:00:01'

---- Add seconds to cleared (w/o time portion) 
---- variable @m1 
SELECT DATEADD(ss,

--- get the time as seconds from the second date
DATEDIFF(ss,0,DATEADD(dd,DATEDIFF(dd,@m2,0),@m2)),

--- remove time part from the first datetime variable
DATEADD(dd,DATEDIFF(dd,0,@m1),0))

Grrrr, I hate datetime algebra

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[rw409168]

Thanks for the code, however I can only execute sqlcommands via ado.net/vb.net so not sure If I can use the code above.

I can't use stored procedures or any code at the server side (rules for a college assignment).

I also just discovered that from the code I wrote to an alias you cant use it within a where clause only an order clause.

Looking at using the alias in a subquery now.

Thanks for the replies

Rob

 

[bborissov]

Just change @m1 and @m2 variables in your query with

b.bookdate and b.startTime

Select *
   from bookings b 
where DATEADD(ss,

--- get the time as seconds from the second date
DATEDIFF(ss,0,DATEADD(dd,DATEDIFF(dd,b.startTime,0),b.startTime)),

--- remove time part from the first datetime variable
DATEADD(dd,DATEDIFF(dd,0,b.bookdate),0)) > GETDATE()

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[rw409168]

Thanks that works a treat.

Im just going to get my head around it now though