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»»

Urgent help with Date comparison in TSQL Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 9:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Dear friends,

I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.

I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,

below is my code of line-
datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )

note the date format for thhis create date in DB is 'date time'

thanks
Dhananjay
Post #1469607
Posted Tuesday, July 2, 2013 9:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
WHERE [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03 12:00:00.000'

Why go complex when there's a simple method?
btw, 12:00:00 is mid day. That is what you want?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1469619
Posted Tuesday, July 2, 2013 9:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
dhananjay.nagarkar (7/2/2013)
Dear friends,

I have requirement - to retrieve order from database that were created before 12:00 AM GMT on June 3, 2013.

I'm using datediff but it is giving me couple records that were created on June 3rd 2.59 AM also,

below is my code of line-
datediff(second,[WorkForce_JobPosting].[Job Posting Create Date_JP] ,'2013-06-03 12:00:00.000')> 0 )

note the date format for thhis create date in DB is 'date time'

thanks
Dhananjay


'2013-06-03 12:00:00.000' is noon on 6/3 so any 6/3 date prior to NOON on that date will be in the result set.

It should be

--midnight the next day
SELECT datediff(second,'2013-06-03 02:59:00.000' ,'2013-06-03 00:00:00.000')
--or
--the last second of the current day
SELECT datediff(second,'2013-06-03 02:59:00.000' ,'2013-06-02 23:59:59.999')


These both return negative values as you are expecting for that particular date input.


 
Post #1469625
Posted Tuesday, July 2, 2013 9:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi friend,

i want it before s before 12:00 AM GMT on June 3, 2013.

can i use it as -

[WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'
?

thanks
Dhananjay
Post #1469632
Posted Tuesday, July 2, 2013 9:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Dear Gail,

I want it before 12.00 AM GMT on June 3rd.

so can I use - [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'? or if not what do you suggest please?

thanks
Dhananjay
Post #1469634
Posted Tuesday, July 2, 2013 9:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Dear Gail,

I want it before 12.00 AM GMT on June 3rd.

so can I use - [WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'? or if not what do you suggest please?

thanks
Dhananjay
Post #1469636
Posted Tuesday, July 2, 2013 9:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
Mid day or mid night? Which are you calling '12:00 AM'?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1469638
Posted Tuesday, July 2, 2013 10:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Hi Gail
Midnight 12.00 AM not Mid Noon since Noon is 12.00 PM

thanks
Dhanajay
Post #1469651
Posted Tuesday, July 2, 2013 10:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
In that case '2013-06-03 00:00:00' as what you originally had ('2013-06-03 12:00:00') is noon, not midnight.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1469654
Posted Tuesday, July 2, 2013 12:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
So Gail this code should work to get me Orders Before June 3rd - 12.00 AM Midnight-

[WorkForce_JobPosting].[Job Posting Create Date_JP] < '2013-06-03'

correct?
Post #1469711
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse