Vchar to date time conversion

Question

Vchar to date time conversion

cafemar1

SSCrazy

Points: 2405
More actions
September 10, 2002 at 1:41 pm

#79697

I know this a simple one, but I can't make to work.
This is the case:
I have a few tables in which I have date fields. The data type in this fields were not set as datetime data type due to client restrictions in their databases (an old system which did not accept any data type other than character and numeric)
When I import their data into SQL Server it works fine, but I cannot add days, weeks, years to the date fields becasue they are varchar, char, or nchar.
I was trying to use CAST and Convert functions as well as DateAdd. Cast is fine if I don't have to calculate any dates, but when I tried to calculate dates (filedate plus 90 days)
For the statement
DATEADD([day], - 90, DATE_CLOSED_DATE)
I get the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value.
Of course if I use
DATEADD([day], - 90, getdate())
all works fine, but ...
Then I used to make sure the syntax was correct,
convert(varchar(12), date_closed_date, 101) as 'Date'
cannot use
convert(datetime (8), date_closed_date, 101) as 'Date'
becasuse of data type difference
Tehn I convined both (cast and dateadd), but
It did not work. It does not matter what I do, dateadd does not work.
I know I am doing something wrong, but I can't figure out what!!
PLEASE HELP!!!!!!
Thank you to all my ghost SQL Server friends out there for your help!!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

This example worked for me. I'm wondering what value you might have in your varchar(12) column. Would you send a couple of values that don't work?

declare @date_closed_date as varchar(12)

set @date_closed_date = '2002-09-10'

select convert(varchar(12), @date_closed_date, 101) as 'Date'

select DATEADD([day], - 90, @DATE_CLOSED_DATE)

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

cafemar1 SSCrazy Points: 2405 More actions · Answer 2

Thanks Gregory. The variable I need to use is getdate() because I am pulling the data WHERE date_closed_date is equal to today's date - 90 days.

select convert(varchar(12), DATE_CLOSED_DATE, 112)AS 'CLOSEDATE'

The above part of my query works fine by itself.

as well as this one

DATEADD(day, -90, GETDATE()) AS 'CLOSEDATE'

But when I used the following it crashes

(DATE_CLOSED_DATE = DATEADD([day], - 90, GETDATE()))

Because date_closed_date is char and getdate() is datetime. So when I use both it comes back with the error message of "out of range"

I appreacite your help though

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

Never could get the 'out of range' message. Here are a couple of date selection examples that might help.

CREATE TABLE #TEMP (

DATE_CLOSED_DATE CHAR(12))

INSERT INTO #TEMP VALUES ('2002-09-11')

INSERT INTO #TEMP VALUES ('2002-08-11')

INSERT INTO #TEMP VALUES ('2002-07-11')

INSERT INTO #TEMP VALUES ('2002-06-13')

INSERT into #TEMP VALUES ('2002-06-14')

INSERT INTO #TEMP VALUES ('2002-05-11')

-- no records found because the date_close_date occurs at midnight, and current time is most

-- likely not midnight

SELECT * FROM #temp where

DATE_CLOSED_DATE = DATEADD([day], - 90, GETDATE())

-- If you want to find the records that closed in the last 90 days then this works

-- down to the minute (note is does not fine 6/13 because that occurred at midnight)

SELECT * FROM #temp where

DATE_CLOSED_DATE >= DATEADD([day], - 90, GETDATE())

-- if you really want to find records that closed day days ago do this:

SELECT * FROM #temp where

DATE_CLOSED_DATE = replace(convert(char(12),DATEADD([day], - 90, GETDATE()),111),'/','-')

DROP TABLE #TEMP

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

cafemar1 SSCrazy Points: 2405 More actions · Answer 4

Thank you for all your input. I did figured it out. Here is the way it worked.

case_tab.DATE_CLOSED_DATE >= CONVERT(varchar(12), DATEADD([day], - 90, GETDATE()), 112)

Instea of trying to convert the date_closed_date from varchar to datetime, I did the opposite. convert getdate() to varchar and then nested the dateadd.

Thank you again for all your help and input.

Nicholas Wang Default port Points: 1463 More actions · Answer 5

Yeah, I once had a problem w/ DATEDIFF and these "ghost friends" here helped me out. You guys are awesome!

I had something like this for DATEADD:

CONVERT(CHAR(10),el.event_time,112) =

CONVERT(CHAR(10), DATEADD(dd, -1,GETDATE()), 112)

I tried to make the two sides of equal side in the same format. And 112 is good for comparison coz there's no . or / or - in this format.