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.
| Author |
Topic |
|
josh2992
Starting Member
2 Posts |
 Posted - 2013-05-30 : 00:26:29
|
| Hi Guys,I am slowly learning SQL and am working on a project, on which I have become stuck. I have a date column that is of smalldatetime type however every time I want to add a row, I want the time part of the smalldate time to be either '1:00:00' '12:00:00' oe '18:00:00' as it relates to a task that is always done at one of those times. The day however must be recorded as 'yesterday'.So I know how to get the date and then take away one day and i know how to set a time value to '1:00:00' but how do i then add these two values together to get a smalldatetime value.Any help is much appreciated, ThanksJosh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 00:54:55
|
| you can use datefunctions like DATEADD,DATEDIFF for thatwhat all values you want to add together? give some sample data and show what you want as output------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
josh2992
Starting Member
2 Posts |
Posted - 2013-05-30 : 02:33:27
|
| ok so to give some background it is relating to the time of a backup. There are three systems that are backed up, one is at 01:00:00, another at 12:00:00 and the third at 18:00:00. This happens every day. So say I want to add the entry for system #1 I already know that it backed up at 1AM, I just need the SQL code to determine what date it was, which will always be the day before the SQL code is executed.So i would have a value @backupDay which would be current day - 1 for example '2013-05-29' and another value @system1BackupTime which would be '01:00:00' (a constant). The output i expect from this is being able to add the two together and get the smalldatetime of '2013-05-29 01:00:00'I hope I am making sense in trying to explain this. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 02:37:02
|
you could simply do a string concatenation for thatDECLARE @backupDay varchar(20),@system1BackupTime varchar(8)SELECT @backupDay ='20130529',@system1BackupTime = '01:00:00' SELECT CAST(@backupDay + ' ' + @system1BackupTime AS datetime) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Manipulating smalldatetime