Pass date field value from a record to a variable

[ADW2005]

I need to pass values from fields named From_Time, To_Time, From_Day to variables.

I then need to update the From_Time and To_Time fields with data from the variable assocaited with From_Day...

I seem to be going round in circles nad have a dozen queries on the go but I'm not getting anywhere. Could someone give me a sense of direction.!!

ADW

 

[dbomrrsm]

declare @From_Time datetime
set @From_Time = select From_Time from yourtable

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[ADW2005]

Well thats what I thought; this is what I've coded;

declare @FromTime datetime
set @FromTime = select From_Time from OldTurtle_ResourceBooking_Bookings

But i then get an syntax error near the word 'select' and if I encapsulate the select statment I get a subquery returned more than 1 value???

 

[dbomrrsm]

right - need more detail then as to what you are doing - the syntax above is correct but you aboviously need a where clause to get the FromTime for each record - give us more detail and table structure/date and what you want before and after and I am sure we will sort this out !!!

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[gmmastros]

Set can only be used when the query ONLY returns 1 value. I got burned on this once or twice myself.

 

[ADW2005]

OK more info on it's way...
We use a DoTNetNuke software portal which is linked to a backend SQL server. We have a problem with the resource bookings module ie we can book rooms etc. Problem is it is storing the dates incorrectly allowing for duplicate bookings to take place. There is an updated module to resolve the problem, but we have to pay for some customisation and incur some downtime -I'd rather avoid that - so I was thinking of running a trigger or sp on the table as soon as the request comes through (I can see this getting complicated).

The 3 main date fields used are, From_Day datetime which is the day you require the room and the From_Time datetime and To_Time datetime are the times you want. However if you book a room for tomorrow, you choose the times but it inserts today's date allowing rooms to be duplicated. So I need to change the From_Time & to_Time values to equal the From_Day.

Let me know if you need more info!!!

 

[dbomrrsm]

so you need to keep the time portion of From_Time and To_Time but change there dates to the date from From_Day ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

Try this:

SELECT @from_time = from_time,
  @to_time = to_time,
  @from_day = from_day
FROM my_table

SET @from_time = CONVERT(varchar(8), @from_day, 112) + ' ' + CONVERT(varchar(12), @from_time, 114)

SET @to_time = CONVERT(varchar(8), @from_day, 112) + ' ' + CONVERT(varchar(12), @to_time, 114)

--James

 

[ADW2005]

Excellent - that passes the info into the variable. Thanks guys!!!

What I need to do now is something like;

If To_Time and From_Time < From_Day then

update mytable

values(@to_time, @from_time)

I think this is OK it's just the If part I'm not sure of...