Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Help with Date and Time Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 10:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:48 PM
Points: 135, Visits: 286
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
Post #1445560
Posted Tuesday, April 23, 2013 10:43 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:55 AM
Points: 1,485, Visits: 1,036
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)
Post #1445572
Posted Tuesday, April 23, 2013 1:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:48 PM
Points: 135, Visits: 286
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.
Post #1445655
Posted Tuesday, April 23, 2013 1:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1445663
Posted Tuesday, April 23, 2013 2:35 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:48 PM
Points: 135, Visits: 286
Hey Sean
I tried this and had to stop the query after 25 mins of running. Any other suggestions would be appreciated.

Doug
Post #1445680
Posted Tuesday, April 23, 2013 2:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1445681
Posted Tuesday, April 23, 2013 2:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 20,795, Visits: 32,710
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.



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)
Post #1445684
Posted Thursday, April 25, 2013 1:52 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:48 PM
Points: 135, Visits: 286
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)

Post #1446697
Posted Thursday, April 25, 2013 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1446704
Posted Thursday, April 25, 2013 6:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:48 PM
Points: 135, Visits: 286
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.
Post #1446758
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse