Shaving Father Time for His Blind Date

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

Transact-SQL (2000)

Shaving Father Time for His Blind Date

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-15 : 13:50:39

I have this code in my SQA:

SELECT distinct NAME,CAST(BIRTHDATE as datetime) as DOB,BIRTHDATE
INTO [Xerxes].[dbo].[new_tab]
FROM [Xerxes].[dbo].[old_tab]
GO

But, of course, I'm getting the time stuff as well as the date
under 'DOB' [1775-11-10 00:00:00.000]. How do I shave the time from the date in the select statement?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-15 : 13:58:12

try executing the code while traveling many times the speed of light. That should shave a little time off...

Are you saying you want a datetime result but at 12:00 am (00:00:00.000) or a varchar value with just the date?

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-15 : 14:01:46

What I want is just the date:

instead of: 1775-11-10 00:00:00.000

I want: 1775-11-10

or, actually 10/11/1775

......what kind of razor do I use?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-15 : 14:06:55

Well then we're talkin' varchar:

That's usually returned as datetime and formatted in the presentation layer but here's the sql version:
select convert(varchar,BIRTHDATE,101).

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-15 : 14:15:09

Uh, TG, uh....that's a sticking point. That's why I'm asking the question. Because later on I'll want to do a comparison on the DOB....like

SELECT distinct NAME,CAST(BIRTHDATE as datetime) as DOB,[BIRTHDATE],TERM
INTO [Xerxes].[dbo].[new_tab]
FROM [Xerxes].[dbo].[old_tab]
WHERE (RIGHT(TERM,4) = 0000 or RIGHT(TERM,4) > 2005)
and (DOB > 09/30/2003)
GO

So....that's why I used the CAST.....any other ideas?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-15 : 14:29:36

Sorry Xerxes I'm still not clear. What datatype is the source <date> now? Once you have datetime datatypes you can easily do comparisons on years or dates or whatever without needing to do conversions to varchar. Some examples:


select	dateadd(year, datediff(year,0,getdate()),0) --use to compare years
        ,datepart(year, getdate()) -- to compare and int to the year portion
	,dateadd(day, datediff(day,0,getdate()),0)  --use compare dates (without time)

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-15 : 14:30:27

SELECT CONVERT(varchar(10),GetDate(),101)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-15 : 15:02:04

Unfortunately, doing this .....

SELECT distinct NAME,CONVERT(varchar(10),BIRTHDATE,120) as DOB,
[BIRTHDATE],TERM
INTO [Xerxes].[dbo].[new_tab]
FROM [Xerxes].[dbo].[old_tab]
GO

....................doesn't make this work....

SELECT distinct NAME, DOB,
[BIRTHDATE],TERM
INTO [Xerxes].[dbo].[new_tab]
FROM [Xerxes].[dbo].[old_tab]
WHERE (RIGHT(TERM,4) = 0000 or RIGHT(TERM,4) > 2005)
and (DOB < '2003-09-30') GO

.......as I'm still getting DOBs outside the range I indicated....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-15 : 15:15:09

try this:


WHERE	(RIGHT(TERM,4 = 0000 or RIGHT(TERM,4) > 2005) 
and	DOB < '2003-09-30'

EDIT:
oh...is that the same thing?

yeah it is...what is the datatype of DOB from old_tab? What values are violating your where clause?

better post some DDL/DML...

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-15 : 15:21:55

quote:
Originally posted by TG

try this:

WHERE (RIGHT(TERM,4 = 0000 or RIGHT(TERM,4) > 2005)
and DOB < '2003-09-30'

EDIT:
oh...is that the same thing? HA,HA, real funny

yeah it is...what is the datatype of DOB from old_tab? What values are violating your where clause?

better post some DDL/DML...

Be One with the Optimizer
TG

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources