converting numeric field to datetime datatype

Question

converting numeric field to datetime datatype

mm-1009269

SSCommitted

Points: 1559
More actions
April 27, 2009 at 1:17 pm

#204055

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 :ermm:

Viewing 8 posts - 1 through 7 (of 7 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Please provide table structures (in the form of CREATE TABLE or Declare @table statements ) and sample data (in the form on INSERT statements) so that the volunteers here can easily see what you are looking at. See the link in my signature for more information on how to do this

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mm-1009269 SSCommitted Points: 1559 More actions · Answer 2

Hi Wayne,

I'm not declaring any variables or creating any tables. and I'd prefer not to create any tables as I'm not the sole owner of the database and IT might yell at me. I guess I'm looking for help if I do indeed need to declare variables in order to convert my data type to datetime for my where statement.

Thanks

Michelle

mm-1009269 SSCommitted Points: 1559 More actions · Answer 3

Here's an example of my data in the created_at column: 20090315060047

Any help is greatly appreciated!

Thanks

Michelle

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Hi Michelle,

The trick here is that you need to actually convert the number to a datetime, not just treat it as a datetime and hope for the best. SQL Server will try and convert your number to a date for you, but it won't do it in a way you expect.

Unfortunately there's no easy way (that I can think of) to do this. So I'm going to go for the way that will at least make sense for you.

You have a date and time in the (numeric) format: YYYYMMDDHHMISS

If you can convert that to this format, then SQL Server will know how to use it: "YYYYMMDD HH:MI:SS"

So the first thing is to convert it to a string: cast(CRR_Orders.CREATED_AT as varchar(17))

But we also need to add those colons and a space. Stuff() will do that nicely: stuff(cast(CRR_Orders.CREATED_AT as varchar(17)), 9, 0, ' ')

This adds a space at the 9th character, replacing 0 characters (so it inserts it.) The same trick for the colons gives us: stuff(stuff(stuff(cast(CRR_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':')

And the final piece is to convert it to a datetime: cast(stuff(stuff(stuff(cast(CRR_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime)

So this expression: and CRR_Orders.CREATED_AT >=dateadd(day,-5,getdate())

... becomes this expression: and cast(stuff(stuff(stuff(cast(CRR_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) >=dateadd(day,-5,getdate())

I would suggest you create a function that you can call to do the conversion for you, so you don't have to continually remember that horrible chunk of code!

One final word of advice: you can look up cast() and convert() and stuff() in SQL Server Books Online. The east way is just to type a keyword in a query window, select it and press Shift+F1.

[/font]

mm-1009269 SSCommitted Points: 1559 More actions · Answer 5

:w00t: Thank you Bruce! It works perfectly!

And thanks for the lesson it made sense to me. It acutally helped me understand what's in my code so I can replicate again.

I'm definately saving this one, you made my day...

Michelle

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

mmunson (4/27/2009)
:w00t: Thank you Bruce! It works perfectly!

[font="Verdana"]You're welcome, Michelle. 😀 I'm glad it worked.

[/font]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Florian Reischl

SSC-Dedicated

Points: 37301

April 27, 2009 at 4:35 pm

#984762

Great explanation, Bruce!