Solved

convert time and access data

Posted on 2013-06-04
11
469 Views
Last Modified: 2013-06-06
I need to access data every half and hour from a site that is in another time zone - two hours behind. The data is time based but a half later. The process runs 24 hours a day. To access the correct data one needs to supply a date and time. so:

my date                     MY TIME      their date      THEIR TIME                      THE DATA
  06/03/2013               2300           06/03/2013    2100                                  2030
  06/03/2013               2330           06/03/2013    2130                                 2100
  06/04/2013               0000           06/03/2013    2200                                 2130
  06/04/2013                 0030         06/03/2013    2230                                  2200
  06/04/2013                 0100         06/03/2013    2300                                  2230
  06/04/2013                 0130         06/03/2013    2330                                   2300
  06/04/2013                 0200         06/04/2013     0000                                   2330
  06/04/2013                 0230         06/04/2013      0030                                    0000
  06/04/2013                 0300        06/04/2013      0100                                   0030
  06/04/2013                 0330        06/04/2013      0130                                    0100
   and so on for the 24 hour day........
                 
The solution must be in sql. I have a method that works with if statements around the 0000-
0100 time fame but I am looking for something more efficient.
   
thanks
0
Comment
Question by:qube09
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39219194
What is your table definition and what output do you expect specifically?
0
 

Author Comment

by:qube09
ID: 39219264
I presume that you are interested in the target table
[date] [smalldatetime] NULL,
[time] [smallint] NULL,
[field1] int null,
[field2] int null

The correct solution will provide their date,   THEIR TIME;THE DATA which will point to field1 and field2 based on my date and my time as in the above example.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39219284
hi,

can you put it this way.


select * from table
where convert(datetime, date + time,103) between
 dateadd(HOUR,-2, convert(datetime,CAST(date2 as datetime) + time2,103)),
dateadd(MINUTE, -90, convert(datetime,CAST(date2 as datetime) + time2,103))

Open in new window



select dateadd(HOUR,-2, convert(datetime,CAST('2012-05-25' as datetime) + '10:00:00',103)),
dateadd(MINUTE, -90, convert(datetime,CAST('2012-05-25' as datetime) + '10:00:00',103))

Open in new window

0
 

Author Comment

by:qube09
ID: 39219415
actually that sql is flagged by my sql 2005 system. The sql will need to be legal sql 2005. Additionally there remains the issue of the date conversion due to the two hour difference.

Basically in the above example my date and my time need to be converted to their date and their time. their date corresponds to the correct data as represented by field1 and field2. The data is a 30 minute offset from their time. so that to get the correct field1 and field2 one needs to subtract 150 mintes from my time. But remember that the time based value in the data must always be either hour + 00 or hour + 30. That is why I started using hour (my time-2) and an if statement combined with an adjustment for date derived by an if statement.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39219441
hi,

do you have your sample query format, how you are looking for?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:qube09
ID: 39219568
if it is of any help this produces something in the neighborhood except that it always needs to end in 0.
SELECT REPLACE(CONVERT(CHAR(5),dateadd(mi,-150,GETDATE()),108), ':', '')
basically I subtract  150 min from my time and convert to military format without ':'. but the issue is how to always end in either 00 or 30
for ex.my time could be 11:17AM IN MILITART TIME less 150 minutes 0847 but the correct value to get the correct data should 0830

and of course the correct date value would stay the same as mine until my time reaches midnight when the correct value for the date field will remain as one day behind until my time reaches 0230
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39221050
line 5 below is what I think you are looking for, the other lines are there to help you follow the logic
select 
  getdate()
, -datediff(minute,0,convert(time,getdate()) ) % 30
, -150 -datediff(minute,0,convert(time,getdate()) ) % 30
, dateadd(minute, -150 -datediff(minute,0,convert(time,getdate()) ) % 30, getdate())
  as the_data

Open in new window

using modulus of 30 we can figure out how many minutes to deduct to get to 00 or 30 (assuming you always 'round-down')
then we also deduct 150 minutes to arrive at the adjusted datetime

from there you can
convert(smalldatetime, <<the_data>>)
convert(time,<<the_data>>)

what I'm unclear on is, are you writing data into their db?
0
 

Author Comment

by:qube09
ID: 39222331
only reading
While I like the overall approach sql 2005 does not recognize 'time' such as in the following:
dateadd(minute, -150 -datediff(minute,0,convert(time,getdate()) ) % 30, getdate())
apparently that is sql 2008 and above
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39222396
try this line 5
select 
  dateadd(day, datediff(day,0, getdate() ), 0)
, datediff(minute,dateadd(day, datediff(day,0, getdate() ), 0), getdate())
, datediff(minute,dateadd(day, datediff(day,0, getdate() ), 0), getdate()) % 30
, dateadd(minute, -150 -(datediff(minute,dateadd(day, datediff(day,0, getdate() ), 0), getdate()) % 30), getdate())
;

Open in new window

0
 

Author Closing Comment

by:qube09
ID: 39223390
excellent approach
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39225037
thanks! (with 4 date functions and 3 getdates, it's an interesting one)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now