SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Urgent help with Date comparison in TSQL


Urgent help with Date comparison in TSQL

Author
Message
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228441 Visits: 46342
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, MVP, M.Sc (Comp Sci)
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


Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2079 Visits: 1721
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.


 
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228441 Visits: 46342
Mid day or mid night? Which are you calling '12:00 AM'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
Hi Gail
Midnight 12.00 AM not Mid Noon since Noon is 12.00 PM

thanks
Dhanajay
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228441 Visits: 46342
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, MVP, M.Sc (Comp Sci)
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


dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 185
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?
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