Format Date/Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Format Date/Time

Author

Topic

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-06 : 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

54 Posts

Posted - 2004-10-06 : 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 - 2004-10-06 : 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

54 Posts

Posted - 2004-10-06 : 02:40:24

well....
table1 = table_A and table2 = table_B
;)

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-06 : 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 - 2004-10-06 : 02:46:55

I see. But I get this error when I run:
Syntax error converting datetime from character string.

What went wrong?

- HELP -

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-06 : 02:52:25

I figured out. As long as I have null values, it will give me this error.

Thanks alot~!! ^-^

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-06 : 03:00:17

strange but i don't get an error with NULL values ...

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-06 : 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 -

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-06 : 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

54 Posts

Posted - 2004-10-06 : 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

11752 Posts

Posted - 2004-10-06 : 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

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-06 : 04:53:14

Thanks alot~!! You all have really been a great help! ^-^

sshelper
Posting Yak Master

216 Posts

Posted - 2006-04-29 : 00:28:24

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-29 : 01:14:31

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

sshelper
Posting Yak Master

216 Posts

Posted - 2006-04-29 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources