SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Format Date/Time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  01:57:54  Show Profile  Reply with Quote
I have two tables, Table_A and Table_B.
I will like to see DOB of both tables and find how many matches.
I wrote the query below.

SELECT COUNT(*) AS Expr1
FROM Table_A,Table_B
WHERE Table_A.DOB = Table_B.DOB

It returns me 0 results.
DOB of both tables is 'VARCHAR'.

However, the DOB of Table_A has data like '01/07/1971'.
The DOB of Table_B has data like '01-JUL-1971'.
Actually they are the same.

Using the query above, can I change the format?

For e.g.
SELECT COUNT(*) AS Expr1
FROM Table_A,Table_B
WHERE Table_A.DOB = Format(Table_B.DOB, dd-mm-yyyy)

Can anyone help? Thanks!


- HELP -

dev45
Yak Posting Veteran

Greece
54 Posts

Posted - 10/06/2004 :  02:38:19  Show Profile  Reply with Quote
try this in the WHERE clause
...WHERE convert(datetime,table1.dob,103) = convert(datetime,table2.dob,103)
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  02:40:14  Show Profile  Reply with Quote
quote:
Originally posted by dev45

try this in the WHERE clause
...WHERE convert(datetime,table1.dob,103) = convert(datetime,table2.dob,103)



Thx for your kind help!

May I ask why is there a "103"? What does it mean?

- HELP -
Go to Top of Page

dev45
Yak Posting Veteran

Greece
54 Posts

Posted - 10/06/2004 :  02:40:24  Show Profile  Reply with Quote
well....
table1 = table_A and table2 = table_B
;)
Go to Top of Page

dev45
Yak Posting Veteran

Greece
54 Posts

Posted - 10/06/2004 :  02:44:19  Show Profile  Reply with Quote
it dictates how the data will be presented
103 will return 2004-07-01 (yyyy-MM-dd)
the sure thing is that by using 103 (or another format) the 2 dates will have the same format

try removing 103 ....
01/07/2004 will be presented as 2004-01-07 and 01-JUL-2004 will be shown as 2004-07-01

check the convert function in BOL (u may find another format that suits your needs best)
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  02:46:55  Show Profile  Reply with Quote
I see. But I get this error when I run:
Syntax error converting datetime from character string.

What went wrong?

- HELP -

Edited by - doggi3 on 10/06/2004 02:47:54
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  02:52:25  Show Profile  Reply with Quote
I figured out. As long as I have null values, it will give me this error.

Thanks alot~!! ^-^


Edited by - doggi3 on 10/06/2004 02:56:07
Go to Top of Page

dev45
Yak Posting Veteran

Greece
54 Posts

Posted - 10/06/2004 :  03:00:17  Show Profile  Reply with Quote
strange but i don't get an error with NULL values ...
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  03:05:09  Show Profile  Reply with Quote
SELECT COUNT(*) AS Expr1
FROM Table_A,Table_B
WHERE Table_A.DOB <> NULL AND
Table_B.DOB <> NULL AND
Table_A.DOB <> 'N.D.' AND
convert(datetime,Table_A.DOB,103) = convert(datetime,Table_B.DOB,103)

It gives me 0 again... It shouldn't...

Where did I go wrong?

- HELP -

Edited by - doggi3 on 10/06/2004 03:05:41
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  03:11:32  Show Profile  Reply with Quote
I think I wrote wrongly above.

DOB of Table_A has data like '07/01/1971'. <-- Notice MM/DD/YYYY
DOB of Table_B has data like '01-JUL-1971'. <-- Notice DD-MMM-YYYY

Will the query still work this way?

- HELP -
Go to Top of Page

dev45
Yak Posting Veteran

Greece
54 Posts

Posted - 10/06/2004 :  03:50:04  Show Profile  Reply with Quote
use
convert(datetime,table_A.dob)
or
convert(datetime,table_a.dob,101)
for the column that has dob in the format : MM/dd/yyyy

Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 10/06/2004 :  04:02:10  Show Profile  Visit spirit1's Homepage  Reply with Quote
weel, all dates are stored in the same format in sql server. so it will work.
try this
select convert(datetime, DOB, 112) from TableA -- this is ISO format yymmdd
all dates should be like that.
if you need to compare only date value not time use this:
SELECT *
from TableA t1, TableB t2
where DATEADD(Day, DATEDIFF(Day, 0, t1.DOB), 0) < DATEADD(Day, DATEDIFF(Day, 0, t2.DOB), 0)

i believe its faster than convert

and use inner join to join tables not
from TableA t1, TableB t2 -> from TableA t1 inner join TableB t2 on (join conditions)


Go with the flow & have fun! Else fight the flow

Edited by - spirit1 on 10/06/2004 04:02:56
Go to Top of Page

doggi3
Yak Posting Veteran

62 Posts

Posted - 10/06/2004 :  04:53:14  Show Profile  Reply with Quote
Thanks alot~!! You all have really been a great help! ^-^


Edited by - doggi3 on 10/06/2004 04:53:53
Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 04/29/2006 :  00:28:24  Show Profile  Visit sshelper's Homepage  Reply with Quote
I know this is an old thread but since this is part of the sticky Frequently Asked Questions, I would just like to contribute a link that converts a date/time value into different date formats:

http://www.sql-server-helper.com/tips/date-formats.aspx

SQL Server Helper
http://www.sql-server-helper.com
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/29/2006 :  01:14:31  Show Profile  Reply with Quote
quote:
Originally posted by sshelper

I know this is an old thread but since this is part of the sticky Frequently Asked Questions, I would just like to contribute a link that converts a date/time value into different date formats:

http://www.sql-server-helper.com/tips/date-formats.aspx

SQL Server Helper
http://www.sql-server-helper.com




That information is available in SQL Server Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

CODO ERGO SUM
Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 04/29/2006 :  23:22:31  Show Profile  Visit sshelper's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

That information is available in SQL Server Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

CODO ERGO SUM



As can be seen from the page, the first part (standard date formats) is based on the CONVERT function in Books Online (in the link you've specified) but with a couple of added columns such as the usage and sample dates. The second part (extended date formats) is a collection of date formats that are not part of the standard formats and are often asked in SQL Server forums.

SQL Server Helper
http://www.sql-server-helper.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000