Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help with Date and Time


Query Help with Date and Time

Author
Message
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
HI

I am selecting from a table and setting a condition in the "Where" clause on a column that has a time and date stamp. When I use set the condition for a 20 min threshold for the time stamp I get data (which is expected)

WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) and a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) or a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())

But when I remove the 20 min threshold from the clause I get no data at all but I should get more data. Can anyone see what i am missing?

WHERE c.CTS_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE()) and a.APP_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE()) or c.CTS_LAST_SAVED_DATE = DATEADD(MINUTE,0,GETDATE())



I even tried removing the reference to time altogether and still get no data.

WHERE c.CTS_LAST_SAVED_DATE = GETDATE() and a.APP_LAST_SAVED_DATE = GETDATE() or c.CTS_LAST_SAVED_DATE = GETDATE()



Doug
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
When you are comparing DATETIME Dates = the date AND time must be equal.

If your data Type is DATE then TIME is eliminated, however GETDATE() will return DATE AND TIME. So they are not equal because there is no implicit conversion from GETDATE to Date

So your where clause needs to consider this.

WHERE My_DATE_Field = CAST(GETDATE() AS DATE)

OR

WHERE My_DATETIME_Field >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)
AND My_DATETIME_Field < CAST(CAST(GETDATE() + 1 AS DATE) AS DATETIME)
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
Both still show no rows. I am looking for rows where the date is right now (today) regardless of the time stamp so I will get the current day each time I run it.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
Maybe something like this?


WHERE cast(c.CTS_LAST_SAVED_DATE as DATE) = cast(GETDATE() as DATE) and cast(a.APP_LAST_SAVED_DATE as DATE) = cast(GETDATE() as DATE) or CAST(c.CTS_LAST_SAVED_DATE as DATE) = cast(GETDATE() as DATE)



At first glance that looks horribly nonSARGable but usually casting a datetime to a date will not render that nonSARGable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
Hey Sean
I tried this and had to stop the query after 25 mins of running. Any other suggestions would be appreciated.

Doug
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
jdbrown239 (4/23/2013)
Hey Sean
I tried this and had to stop the query after 25 mins of running. Any other suggestions would be appreciated.

Doug


Well we are all just guessing here because we can't see what you see. Can you post ddl and sample data so we can help? Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
Here is the problem, we can't see what you see. This means with as little information as you provided, all we can do is take shots in the dark and hope we hit the target.

Barring direct access to your server and the database, you really need to provide us with as much information as possible to be able to help you. Please imagine if we had asked you for help only providing the info you provided, how much could you do to help us knowing nothing else?

Help us help you, and you can do this my reading and following the instructions in the first article I reference below in my signature block. The article will walk you through everything you need to post and how to do it to get the best possible answers quickly.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
Here is the original query with the time stamp

Select    a.app_key AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER, 
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey
WHERE c.CTS_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND c.CTS_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) and a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE()) or a.APP_LAST_SAVED_DATE > DATEADD(MINUTE,-50,GETDATE()) AND a.APP_LAST_SAVED_DATE < DATEADD(MINUTE,-30,GETDATE())



Here is a sample result set

 APP_NUMBER   CREDIT_AMOUNT   DEALER_NUM   APP_CONTRACT_NUMBER   APPLICATION_STATUS   BOOKING_DATE   CUSTOMER   CUSTOMER_DBA   CCAN   DATE_ENTERED   FEDERAL_ID
955315 2281.00 7183385800 NULL NULL NULL Zirh Limited Liability Company NULL 1347518 04/25/2013 NULL
955192 6850.00 9549701691 NULL Approved NULL Claremore Tire Center, Inc. NULL 1380831 04/25/2013 NULL
955229 20000.00 2677597066 NULL Approved - SFP NULL Steven S Levine DMD NULL 1380854 04/25/2013 NULL
953008 46000.00 6196961699 NULL Approved NULL Tom Paige Catering Company NULL 1344979 04/12/2013 NULL
955252 17601.00 8008168138 NULL Approved NULL American Tire & Auto Care, Inc NULL 1380871 04/25/2013 72-0944075



Here is the query with no time stamp where I get no results.

 Select    a.app_key AS APP_NUMBER, a.CREDIT_AMOUNT, REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM, c.cts_contract_number AS APP_CONTRACT_NUMBER, 
CASE co.CO_UD_APP_STATUS_TBDESC WHEN 'PO Issued' THEN CASE a.APP_DECISION_CODE WHEN 7 THEN 'Booked' ELSE 'PO Issued' END WHEN 'Approved' THEN
CASE co.CO_LESSOR WHEN '421' THEN 'Approved - SFP' WHEN '423' THEN 'Approved - SFP' WHEN '424' THEN 'Approved - SFP' ELSE 'Approved' END ELSE co.CO_UD_APP_STATUS_TBDESC
END AS APPLICATION_STATUS, CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER, CONVERT(XML, a.APP_DATA, 0 ).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA,
CASE WHEN LEN(CONVERT(XML, a.APP_DATA, 0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8 THEN CONVERT(XML, a.APP_DATA,
0 ).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)') ELSE NULL END AS CCAN, CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA,
0 ).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED, CONVERT(XML, a.APP_DATA, 0 ).value('(//FED.ID/node())[1]', 'VARCHAR(50)')
AS FEDERAL_ID
FROM dbo.APPLICATION AS a LEFT OUTER JOIN
dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY LEFT OUTER JOIN
dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0 ).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY LEFT OUTER JOIN
dbo.Contract_Setup AS c on a.app_key = c.cts_app_fkey WHERE c.CTS_LAST_SAVED_DATE = CAST(DATEADD(day,1,CURRENT_TIMESTAMP) as DATE) and a.APP_LAST_SAVED_DATE = CAST(DATEADD(day,1,CURRENT_TIMESTAMP) as DATE) or c.CTS_LAST_SAVED_DATE = CAST(CURRENT_TIMESTAMP as DATE)


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16582 Visits: 17024
I am going to go out on a limb here and guess that you didn't bother to read the article that was suggested by myself and Lynn? Look at what you have posted so far in this thread and ask yourself if you honestly think you have posted enough information for you to answer this.

We can't begin to post a solution because we don't tables and/or data to work with here. Help us to help you and will be rewarded with tested and fast code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jdbrown239
jdbrown239
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 390
Sean
I did read the article but the query involves joins on four tables along with converting XML data. Unfortunately I can't figure out an easy way to provide a single sample table you could build and have you load sample data with dates and time stamp to illustrate the problem. If I could I probably wouldn't be asking the question I have on the 'Where' clause so I provided as much information as I could. Posting this question as the article suggest its a bit beyond my skill set right now. Thanks for the info and trying to assist me. Maybe my next quest will be a little easier to post in the manner suggested.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search