Crystal Reports convert char value into dates

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: convert char value into dates

Author	Message
ricky_vegas Newbie Joined: 03 Jan 2008 Location: United States Online Status: Offline Posts: 15	Topic: convert char value into dates Posted: 24 Jan 2008 at 9:52am
	I am working with 3rd party software that stores time in a SQL database field as char For example: 33625 corresponds to 01/23/08. I would like to convert this char value to datetime format (mm/dd/yy). Any help would be greatly appreciated. rick
	IP Logged


Lugh Senior Member Joined: 14 Nov 2007 Online Status: Offline Posts: 377	Posted: 24 Jan 2008 at 10:07am
	OK, are you sure you mean char, and not, say int? How does 33625 correspond to 01/23/2008?
	IP Logged

ricky_vegas Newbie Joined: 03 Jan 2008 Location: United States Online Status: Offline Posts: 15	Posted: 24 Jan 2008 at 10:19am
	if I run a SELECT CAST(33625 AS DATETIME) I get: 1992-01-24 00:00:00.000 I apologize, I thought 33625 was Jan 23rd. However where does the 1992 come from?
	IP Logged

ricky_vegas Newbie Joined: 03 Jan 2008 Location: United States Online Status: Offline Posts: 15	Posted: 24 Jan 2008 at 10:34am
	the datatype for ALL my fields is char. I believe the db was designed in 1980 and has never been updated.
	IP Logged

ricky_vegas Newbie Joined: 03 Jan 2008 Location: United States Online Status: Offline Posts: 15	Posted: 24 Jan 2008 at 2:20pm
	Using SELECT DATEADD(DAY, 33635,'1916-01-01') I get the result I wanted: 2008-01-23 I got this answer from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96118
	IP Logged

ricky_vegas Newbie Joined: 03 Jan 2008 Location: United States Online Status: Offline Posts: 15	Posted: 24 Jan 2008 at 3:05pm
	I created the following formula and my report worked great: DateAdd ('D',ToNumber({SH10.TOT_EST_SHIP_CODE}),CDate('1916-01-01'))
	IP Logged

Lugh Senior Member Joined: 14 Nov 2007 Online Status: Offline Posts: 377	Posted: 25 Jan 2008 at 4:55am
	I'm glad you got this worked out. And, you have my sincerest sympathies. I don't think this is the last, or probably the most difficult, headache you will have with this database. Good luck.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum