Get all data for the next 24 hours from selected date in sql

I am trying to get all data for the next 24 hours from selected date and the selected date comes from this statement:
select selected_Date from myTable where DT_VAR = '03/12/2015 : 0800'

Open in new window

I don't want to pull any data that is before 3/12/2015 : 0800 but i need all data  for the next 24 hours from this date.  i know i have to self join the table but not sure how to do this.  Please help
moe57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select * from sometable
where datefield >= ( select top 1 selected_Date from myTable where DT_VAR = '03/12/2015 : 0800' )
  and datefield < ( select top 1 dateadd(Hour, 8, selected_Date) from myTable where DT_VAR = '03/12/2015 : 0800' )

Open in new window

see also this article:http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you use this SELECT will always return the last 24h data:
select selected_Date from myTable where DT_VAR >= GETDATE()-1

Open in new window

0
moe57Author Commented:
i am getting this error: Conversion failed when converting date and/or time from character string.  The DT_VAR is nvarchar
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Due to the date format of your varchar field isn't a good idea to compare strings so it's better to convert it to datetime:
select selected_Date from myTable where CAST(DT_VAR AS datetime) >= GETDATE()-1

Open in new window

NOTE: The conversion may fail if the date and time aren't well formatted in DT_VAR column.
0
moe57Author Commented:
Vitor, i have to run the statement below first in order to to get the selected_Date, then after that i need to find all data for the next 24 hours from the selected_date.  with your solution, i don't see where i run this statement:
select selected_Date from myTable where DT_VAR = '03/12/2015 : 0800'

Open in new window

thanks for your help
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Where the '03/12/2015 : 0800' come from?
Also isn't in a SQL Server datetime format. Should be at least like this '03/12/2015 08:00'
0
moe57Author Commented:
it is in my table, i have it as nvarchar and i can't changes it because it is not my table.
0
PortletPaulfreelancerCommented:
Hi.  The very first thing we need to sort out here is the data type of the field [DT_VAR]

A: It seems this is NVARCHAR
B: It is used to store date/time information  (using nvarchar is NOT ideal for this, but we just have to move on)

BUT
What isn't known with absolute certainty is the FORMAT of that date/time information
AND this really does matter (because it is NVARCHAR data)

this is the only clue we have about the format:
                                                        where DT_VAR = '03/12/2015 : 0800'

That is a very strange date/time string!
that colon position I have never seen before and there is nothing separating minutes from hours.

I suggest you provide us with a broader sample of data from that column. Can you do that please?

Can you also confirm what version of SQL Server you are using?
I would specifically like to know if we can use TRY_CAST()
{available from SQL Server 2012 onward}
0
PortletPaulfreelancerCommented:
The following will only work if  you have made a small typo in your question

I am hoping
           where DT_VAR = '03/12/2015 : 0800'

is really
           where DT_VAR = '03/12/2015 08:00'

IF (and only if) that assumption is true then this may work:
select
      dt_var
from MyTable
WHERE DT_VAR >= '03/12/2015 08:00'
AND DT_VAR < convert(nvarchar(16),DATEADD(DAY,1,convert(datetime, '03/12/2015 08:00' ,101)),101)
;

Open in new window


If there is an error please provide the full error message in your next comment
(along with more examples from that column and the version of sql server you are using for this query)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moe57Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.