[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query - DateTime

Posted on 2007-07-20
7
Medium Priority
?
276 Views
Last Modified: 2012-05-05
Hi,

I have a table with a lot of datetimes.

SELECT dtField
FROM MyTable
ORDER BY dtField DESC

Gives me:

ResultNr         dtField
1                     2007-05-22
2                     2007-05-21
3                     2007-05-18
..                     ..

How can I make a SELECT query that returns me a single row:

select...from MyTable......where dtField = 'SomeDate'

...so that:
- If SomeDate exists, I get that.
- If SomeDate doesn´t exist, I get the date that happened just before that.

F.EX select.....from MyTable where dtField = '2007-05-22'    -- returns me the date 2007-05-22

F.EX select.....from MyTable where dtField = ' 2007-05-20'    -- returns me the date2007-05-18 (because  ' 2007-05-20' doesn't exist)

Does anybody know how to do this?

Thank you.
0
Comment
Question by:Endelm
  • 3
  • 2
  • 2
7 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 19531255
select top 1 dtfield
from mytable
where dtfield=
(select max(dtfield) from mytable where dtfield<='somedate')
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 19531262
select top 1 dtfield from mytable where dtfield <= SomeDate order by dtfield desc
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 19531275
in the main query you can also select other fields that you need, not only dtfield.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Endelm
ID: 19531525
Hi,

I tried both your solutions, and it didn´t work.

See the example that I've created:

create table dbo.TestTable
(date datetime)

insert into dbo.TestTable (date)
values ('2007-05-22 14:00:00.00')

insert into dbo.TestTable (date)
values ('2007-05-21 15:00:00.00')

insert into dbo.TestTable (date)
values ('2007-05-18 16:00:00.00')

--------
select top 1 date
from dbo.TestTable
where date=
(select max(date) from dbo.TestTable where date<='2007-05-22')

and

select top 1 date
from dbo.TestTable
where date <= '2007-05-22' order by date desc

both return me the result:
'2007-05-21 15:00:00.000'

(but I should have gotten the date '2007-05-22 14:00:00.00')

Any ideas?

Thank you.

0
 

Author Comment

by:Endelm
ID: 19531551
I only managed to get it to work when I inserted:
2007-05-22
2007-05-21
2007-05-18

instead of:
2007-05-22 14:00:00.00'
2007-05-21 15:00:00.00'
2007-05-18 16:00:00.00'

Do you know why it doesn't work with the latter one?

Thank you.
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 19531582
because 5/22 14:00 is > 5/22

one way to get around that is:

select top 1 date
from dbo.TestTable
where date <= '2007-05-22 23:59:59' order by date desc
0
 

Author Comment

by:Endelm
ID: 19531643
Genious! Thank you so much :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 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