converting varchar to dattime format

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 2005 Forums

Transact-SQL (2005)

converting varchar to dattime format

Author

Topic

mmunson
Starting Member

10 Posts

Posted - 2009-04-27 : 15:44:29

Hello, I hope I'm posting to the right forum.
I have a created_at column which is a numeric (15,0) date type and it contains dates and I need this to be a date format. (I know it should be a datetime datatype, but the data is being transported through our middleware and it has to stay like that.)

This is my query:
SELECT CRR_StatusCodes.TRANSACTION_ID, CRR_Partners.PARTNER, CRR_Partners.PARTNER_FCT, CRR_Partners.DESCRIPTION,
CRR_StatusCodes.SYSTEM_STATUS, CRR_Partners.ADDRESS, CRR_Orders.CREATED_AT
FROM CRR_Partners INNER JOIN
CRR_Orders ON CRM_Partners.PARTNER_ID = CRR_Orders.PARTNER_ID INNER JOIN
CRR_StatusCodes ON CRR_Orders.TRANSACTION_ID = CRR_StatusCodes.TRANSACTION_ID INNER JOIN
CRR_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRR_PartsLabor.TRANSACTION_ID
WHERE (CRR_StatusCodes.SYSTEM_STATUS <> 'Completed') AND (CRR_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')) AND
(CRR_PartsLabor.DESCRIPTION = 'Labor') and CRR_Orders.CREATED_AT >=dateadd(day,-5,getdate())
I get the error, 'Arithmetic overflow error converting expression to data type datetime' because my created_at isn't a date format. I've been trying all day to convert this field but I feel like I'm hitting my head against a brick wall. I asked our dba guy about this but his suggestions didn't work.

I'm kind of a sql beginner so help (in detail) would be greatly appreciated!

Thanks
Michelle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 15:46:55

Can you provide a sample of the numeric value?

E 12°55'05.63"
N 56°04'39.26"

mmunson
Starting Member

10 Posts

Posted - 2009-04-27 : 15:49:43

Peso,

Here's an example: 20090315060047

Thanks

quote:
Originally posted by Peso

Can you provide a sample of the numeric value?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 15:53:00

SELECT CAST(STUFF(STUFF(STUFF(STR(Col1, 14, 0), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

mmunson
Starting Member

10 Posts

Posted - 2009-04-27 : 16:22:53

Peso, that's great! It works. I tried working with the stuff function but I wouldn't have been able to come up with that!

I'm risking sounding like a complete idiot, but could you plesae help me insert that into my query in my first post. I'm a little lost on how to do that. Like I said, I'm a bit of a newbie so I have a alot of idiot questions!

Thanks Peso
Michelle

quote:
Originally posted by Peso

SELECT CAST(STUFF(STUFF(STUFF(STR(Col1, 14, 0), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 16:29:58

[code]SELECT CRR_StatusCodes.TRANSACTION_ID,
CRR_Partners.PARTNER,
CRR_Partners.PARTNER_FCT,
CRR_Partners.DESCRIPTION,
CRR_StatusCodes.SYSTEM_STATUS,
CRR_Partners.ADDRESS,
CAST(STUFF(STUFF(STUFF(STR(CRR_Orders.CREATED_AT, 14, 0), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME) AS CreatedAt
FROM CRR_Partners
INNER JOIN CRR_Orders ON CRM_Partners.PARTNER_ID = CRR_Orders.PARTNER_ID
INNER JOIN CRR_StatusCodes ON CRR_Orders.TRANSACTION_ID = CRR_StatusCodes.TRANSACTION_ID
INNER JOIN CRR_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRR_PartsLabor.TRANSACTION_ID
WHERE CRR_StatusCodes.SYSTEM_STATUS <> 'Completed'
AND CRR_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
AND CRR_PartsLabor.DESCRIPTION = 'Labor'
and CRR_Orders.CREATED_AT >= DATEADD(DAY, -5, GETDATE())[/code]

E 12°55'05.63"
N 56°04'39.26"

mmunson
Starting Member

10 Posts

Posted - 2009-04-27 : 16:44:08

Thanks Peso, I figured it out!

quote:

Originally posted by Peso

SELECT		CRR_StatusCodes.TRANSACTION_ID,
		CRR_Partners.PARTNER,
		CRR_Partners.PARTNER_FCT,
		CRR_Partners.DESCRIPTION,
		CRR_StatusCodes.SYSTEM_STATUS,
		CRR_Partners.ADDRESS,
		CAST(STUFF(STUFF(STUFF(STR(CRR_Orders.CREATED_AT, 14, 0), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME) AS CreatedAt
FROM		CRR_Partners
INNER JOIN	CRR_Orders ON CRM_Partners.PARTNER_ID = CRR_Orders.PARTNER_ID
INNER JOIN	CRR_StatusCodes ON CRR_Orders.TRANSACTION_ID = CRR_StatusCodes.TRANSACTION_ID
INNER JOIN	CRR_PartsLabor ON CRM_Orders.TRANSACTION_ID = CRR_PartsLabor.TRANSACTION_ID
WHERE		CRR_StatusCodes.SYSTEM_STATUS <> 'Completed'
		AND CRR_Partners.PARTNER_FCT IN ('ZRSM', '00000052', '00000056')
		AND CRR_PartsLabor.DESCRIPTION = 'Labor'
		and CRR_Orders.CREATED_AT >= DATEADD(DAY, -5, GETDATE())

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 01:05:39

You're welcome.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources