| Author |
 Topic  |
|
|
doggi3
Yak Posting Veteran
62 Posts |
 Posted - 10/06/2004 : 01:57:54
|
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
|
try this in the WHERE clause
...WHERE convert(datetime,table1.dob,103) = convert(datetime,table2.dob,103) |
 |
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 10/06/2004 : 02:40:14
|
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 - |
|
|
|
dev45
Yak Posting Veteran
Greece
54 Posts |
Posted - 10/06/2004 : 02:40:24
|
well....
table1 = table_A and table2 = table_B
;) |
|
|
|
dev45
Yak Posting Veteran
Greece
54 Posts |
Posted - 10/06/2004 : 02:44:19
|
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) |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 10/06/2004 : 02:46:55
|
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 |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 10/06/2004 : 02:52:25
|
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 |
|
|
|
dev45
Yak Posting Veteran
Greece
54 Posts |
Posted - 10/06/2004 : 03:00:17
|
strange but i don't get an error with NULL values ...
|
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 10/06/2004 : 03:05:09
|
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 |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 10/06/2004 : 03:11:32
|
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 - |
|
|
|
dev45
Yak Posting Veteran
Greece
54 Posts |
Posted - 10/06/2004 : 03:50:04
|
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
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 10/06/2004 : 04:02:10
|
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 |
|
|
|
doggi3
Yak Posting Veteran
62 Posts |
Posted - 10/06/2004 : 04:53:14
|
Thanks alot~!! You all have really been a great help! ^-^
|
Edited by - doggi3 on 10/06/2004 04:53:53 |
|
|
|
sshelper
Posting Yak Master
213 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
|
|
sshelper
Posting Yak Master
213 Posts |
Posted - 04/29/2006 : 23:22:31
|
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 |
|
|
| |
Topic |
|
Format Date/Time
