Issue with Date Format

Question

Issue with Date Format

pawana.paul

SSCommitted

Points: 1686
More actions
September 7, 2012 at 10:41 am

#261293

Hello Everyone,
I have an issue when pulling the data from multiple tables.
Table A has column Date_Created
Table B has column Date_Created
The format is somethine "2012-09-06 13:04:01.067". There is a time difference between columns of 2 tables and I want to compare with dates only and retrieve the data. If I compare both the columns it will not display any data due to time difference. Please let me know how ignore time and compare both columns and retrieve data.
Thanks in advance
Regards,
Pawana Paul

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

pawana.paul (9/7/2012)
Hello Everyone,
I have an issue when pulling the data from multiple tables.
Table A has column Date_Created
Table B has column Date_Created
The format is somethine "2012-09-06 13:04:01.067". There is a time difference between columns of 2 tables and I want to compare with dates only and retrieve the data. If I compare both the columns it will not display any data due to time difference. Please let me know how ignore time and compare both columns and retrieve data.
Thanks in advance
Regards,
Pawana Paul

Can you provide the code you are currently using? Hard to tell you what needs to change when don't know what you are currently doing.

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

You do this with some pretty simple date math. Just use the beginning of the day when comparing.

See this article from Lynn about some common datetime routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You might find this useful.

SELECTCAST( GETDATE() AS DATE),

DATEADD( DD,DATEDIFF(DD, 0, GETDATE()),0)

If you just need dates with no time, you should store them that way and even with the correct data type (date for 2008 or higher and datetime or smalldatetime for 2005 and lower).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pawana.paul SSCommitted Points: 1686 More actions · Answer 4

Hello Lynn,

Thanks for your response.

Below is the syntax of the original query.

SELECT A.COL1, A.COL2, B.COL3, B.COL4 FROM TABLE A JOIN TABLE B ON A.DATE_CREATED = B.DATE_CREATED

Thanks in advance,

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

does this help?

INNER JOIN TableB AS B ON DATEADD( dd , DATEDIFF( dd , 0 , A.date_created ) , 0 ) = DATEADD( dd , DATEDIFF( dd , 0 , B.date_created ) , 0 )

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Luis Cazares (9/7/2012)
You might find this useful.
SELECTCAST( GETDATE() AS DATE),
DATEADD( DD,DATEDIFF(DD, 0, GETDATE()),0)
If you just need dates with no time, you should store them that way and even with the correct data type (date for 2008 or higher and datetime or smalldatetime for 2005 and lower).

I agree that if all you want is date then you should use date as the datatype.

See if this works.

SELECT A.COL1, A.COL2, B.COL3, B.COL4 FROM TABLE A JOIN TABLE B ON cast(A.DATE_CREATED as date) = cast(B.DATE_CREATED as date)

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pawana.paul SSCommitted Points: 1686 More actions · Answer 7

pawana.paul

SSCommitted

Points: 1686

September 7, 2012 at 11:19 am

#1535197

Thanks very much. It should help.

Regards,