-1

I want a default on a column(smalldatetime data type) where the time value in each row is current time + 6hrs.

I tried using the Getdate() function but it doesnt update. Instead it just provides a stamp of when the rows were created. You can see that I tried the script in this post. I thought it had worked but when I checked the table the next day the time had never changed.I would like to set the Default value for a column to Current Time + 6hrs

Thanks in advance for any help offered.

Community
  • 1
  • 1
frustrationmultiplied
  • 439
  • 8
  • 18
  • This question does not make any sense. You asked the question and it was answered. What exactly do you want? You say it does not update... do you mean you want the current time +6 from when you query? – Hogan Feb 20 '15 at 18:35
  • whenever i look at the column i want to see that the time in the column is equal to whatever the current time is + 6 hours. the reason is that the column is used to hold the departure times for flights. the nearest departure time can be 6 hours from the current time as some airlines allow this – frustrationmultiplied Feb 20 '15 at 18:39
  • What do you mean by looking at the column? – Hogan Feb 20 '15 at 18:40

1 Answers1

0

Add the following field to your view:

DATEADD(hour, 6, GETDATE()) AS Minimum_Departure_Time

Since you want it to change when you look at it, there is no reason to put it in the table. Just put it in your view since the view is dynamically created everytime it is run.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • When i go to update the table and input flight records i want to see that the time in the column is current time +6hrs . if i need to change the time then i will.I have a constraint on the column to make sure times entered must be at least 6hrs in advance . – frustrationmultiplied Feb 20 '15 at 18:44
  • what do you mean by "make sure"? what do you mean by "any time entered"? – Hogan Feb 20 '15 at 18:45
  • i mean by make sure that no departure time can be less than 6hrs away from current time. by any time entered i mean any departure time entered in the column for a particular flight. – frustrationmultiplied Feb 20 '15 at 18:50
  • so when you are doing an update you want to make sure the departure time is 6 hours from now? ok, how are you updating the table -- this business rule is more suited for your UI application since it can alert the user for a new value. – Hogan Feb 20 '15 at 18:54
  • im updating using a view and then editing the rows in the view – frustrationmultiplied Feb 20 '15 at 19:14
  • or, add a column like I show in my answer above to your view. – Hogan Feb 20 '15 at 19:42
  • can you explain what you meant by last comment – frustrationmultiplied Feb 20 '15 at 19:45
  • in your view add the codes `DATEADD(hour, 6, GETDATE()) AS Minimum_Departure_Time` to the select list. This will give you a column with the value you want. – Hogan Feb 20 '15 at 19:50
  • surely this will give just a static date. what is wrong with updating through a view? – frustrationmultiplied Feb 20 '15 at 19:53
  • I've no idea what you are asking. – Hogan Feb 20 '15 at 19:54
  • the current date will display initially. what happens when i close the view and reopen it a few days later and need to input new flight records. the date will show the same date from when the getdate function was created – frustrationmultiplied Feb 20 '15 at 19:58
  • In my original post i used this to create a default" DEFAULT DATEADD(hour, 6, GETDATE()) FOR DepTime". It resulted in a static date being shown in the column i.e it did not update every day.Whats the difference between the above and the code you are advising – frustrationmultiplied Feb 20 '15 at 20:04
  • I'm not suggesting you put the calculation as default value of a table column because TABLES ARE STORED IN A FILE. I'm suggesting you add the calculation to a view which are EXECUTED AT RUNTIME. These are basic fundamentals of how SQL databases work. – Hogan Feb 20 '15 at 20:07
  • very good. thanks for that explanation. i will add it to the view. you said earlier that i should stop updating in the view. is there a problem with doing updating this way. – frustrationmultiplied Feb 20 '15 at 20:09
  • My comment about "stop doing that" was slightly in jest. I'd suggested prior that you put the UI validation logic in the UI layer. You suggested that you were updating in a view which some how prevented you from having appropriate separation of concerns. So I said stop since it clearly a bad design if it caused this.It has nothing to do with the details of the technique which while not a leading practice is not inherently wrong. I still think there is something wrong with validating user input at the database tier. If I were you I would try to find a better design for user data validation. – Hogan Feb 20 '15 at 20:13
  • I dont have a GUI atm. if u can suggest a better way of updating please do. Btw i have accepted the answer in quotes below. can you post it up as the answer in a new post. i cant do so as i cant answer my own question. it might help somone else lookin for the solution rather than having to read through the comments. thanks for all your help. "in your view add the codes DATEADD(hour, 6, GETDATE()) AS Minimum_Departure_Time to the select list. This will give you a column with the value you want." – frustrationmultiplied Feb 20 '15 at 20:19