Date query

Question

Post reply

Date query

Dingo1Dave

SSC-Addicted

Points: 431
More actions
April 15, 2011 at 8:11 am

#236703

Hi,
I am new to SQL and am in the process of writing code to extract out information based on dates.
I have a table with the relevant account information and an 'Effective date' and 'Expiration date' columns. I am looking to extract the data out on a particular day. Say I choose the 31st March 2011, I have this in my code as
EffectiveDate <= '2011-03-31'
and ExpirationDate >= '2011-03-31'
and this works fine.
What I am trying to do is set up another query for now. I.e. the date when the query is run without having to go in and manually change the date. So if it was run today, I want it to return all accounts that are 'live' as at todays date.
Any help or guidance on this would be appreciated, as I'm about to find a brick wall to bang my head against!
Thanks
Dave

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

If you use GetDate() in place of the hard coded date you will get anything for today.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 2

Hi Dan,

Thanks for your quick reply.

I have amended my code as follows:

select *

from dbo.TvwContractView

where

BusinessUnit = 'Property_DATA_LIBRARY'

and Book = 'Property - Live'

and [Type] = 'Layer'

and GETDATE()

and [Status] = 'Bound'

But get the error below:

Msg 4145, Level 15, State 1, Line 8

An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

I'm sure the reason is simple - just not for me!

Aprreciate your help.

Many Thanks

Dave

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

Sorry you want something like

EffectiveDate > Getdate()-1

this would effectivly give you everything for today.

Remember that get date would translate into the exact date and time. So lets say you run this at noon. the date would be returned as '04/15/11 12:00:00.000'

I mention this since as it is an important concept to understand when you are learning SQL. The date is stored with the time. which can affect you results. So lets say you changed your where clause to be

EffectiveDate = Getdate()

Unless there was a record in the table with the exact date and time includeing milliseconds then you would not get any results. so using greater than GetDate() -1 ensures I get all records for today regardless of time.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

SELECT *

FROM dbo.tvwcontractview

WHERE businessunit = 'Property_DATA_LIBRARY'

AND book = 'Property - Live'

AND [Type] = 'Layer'

--Today at 00:00:00

AND effectivedate >= Dateadd(DAY, Datediff(DAY, 0, Getdate()), 0)

--Tomorrow at 00:00:00

AND effectivedate < Dateadd(DAY, Datediff(DAY, 0, Getdate()), 1)

AND [Status] = 'Bound'

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

For an extensive list of using and manipulating DATE and TIME refer to this posting by Lynnpettis

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

For comparison of just dates in a DATETIME column. Set the time portion to midnight by using.

SELECT dateadd(day,datediff(day, 0,GETDATE()),0)

Result:

2011-04-15 00:00:00.000

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dingo1Dave SSC-Addicted Points: 431 More actions · Answer 6

Thanks Dan.

This now gives me all accounts that have an effective date after todays date. I am trying to find all accounts that are live as at todays date. I.E. if the effective date is before todays date and the expiration date is after todays date.

Regards

Dave