0

I have little experience with SQL Server and need some help as our DBA is no longer with us and we are in the process of looking for a new one.

Background: We are using two columns in a table, customVariable2 and customVariable3. customVariable2 is the first timestamp and customVariable3 is the second timestamp. The time format is HH:MM:SS PM PDT.

I need to find the difference in time between customVariable2 and customVariable3 and then show that difference in another column. If it returns a value in seconds or minutes that is fine.

I am not sure what other information is needed from me for the community to be able to help. Please let me know if more information is needed and i can provide that.

Again I am a completely green when it comes to writing queries.

Thanks in advance for any help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3892880
  • 5
  • 2
  • would http://msdn.microsoft.com/en-us/library/ms189794.aspx DATEDIFF() work for you? – Christopher Johnson Jul 30 '14 at 19:32
  • I have tried DATEDIFF and I might not be formatting it properly, but i can't seem to get it to work because it expects a full date not just a time of day. – user3892880 Jul 30 '14 at 19:33
  • 2
    what are the data types for customervariable2 and 3. Can you provide an example? – SQLChao Jul 30 '14 at 19:34
  • 2
    datediff will accept a time, date, smalldatetime, datetime, datetime2, or datetimeoffset for the start/end parameters. That's most likely the easiest route as Christopher Johnson mentioned. Giving us an example of your code will help us narrow down your needs. – Luke Wyatt Jul 30 '14 at 19:37

1 Answers1

0

This works:

select DATEDIFF(second,'12:12:12 PM','1:13:13 PM')

but this does not:

select DATEDIFF(second,'12:12:12 PM PDT','1:13:13 PM PDT')

If the data is in varchar's and includes the "PDT", I would suspect what you need to do is 

select DATEDIFF(second,replace(customVariable2,' PDT',''), replace(customVariable3,' PDT',''))

After the additional question in the comment, to store the result of the difference in timestamps:

update tablename
set customVariable4 = DATEDIFF(second, replace(customVariable2,' PDT',''), replace(customVariable3,' PDT',''))

If the column to store the difference (customVariable4 in my sample code) in doesn't already exist, you'll need to add it to the table.

DerekCate
  • 306
  • 1
  • 5
  • Now how do i add a new column to the results i get with the returned value. Currently it opens a new results table with the time difference. I need to have it be inserted into the original table. If more informatino is needed please let me know and i'll add it. – user3892880 Jul 30 '14 at 20:52
  • So the answer depends on if the column already exists or if you need to add it. I'll edit my answer, still too new at actually posting. – DerekCate Jul 31 '14 at 13:28