Convert a field from numeric type to date format

Question

Post reply

Convert a field from numeric type to date format

SQL SERVER ROOKIE

Ten Centuries

Points: 1032
More actions
November 19, 2013 at 7:52 am

#301547

Hi,
DB version: MS SQL server 2008 R2
I have a date field which stores date as numeric.
How can I convert it from numeric to date format
Date is stored in YYYYMMDD format ,datatype is numeric(8,0)
the field also has nulls and 0's
.
I tried using
CONVERT(DATETIME, CAST(I290.[MYDATEFIELD] AS VARCHAR(8)), 112)AS NEWDATE
This is the error i get
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Thanks
SM

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

the field also has nulls and 0's

What date would you like 0 converted to?

John

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 2

Hey John,

For 0's,invalid dates it should display nulls

Thanks

SM

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Try with:

CAST(CAST( NULLIF(NumericDatefield, 0) AS char(8)) AS datetime)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 4

You are the man :-)!! Luis

Perfect that's what I was looking for

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

And then it would be HIGHLY advisable to change your schema that you store dates in a date column instead of numeric. It will save you countless hours of pulling out your hair because you got garbage into your database that is unable to be converted to the datatype it should be in the first place.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Sean Lange (11/19/2013)
And then it would be HIGHLY advisable to change your schema that you store dates in a date column instead of numeric. It will save you countless hours of pulling out your hair because you got garbage into your database that is unable to be converted to the datatype it should be in the first place.

+1

Everyday I struggle because we have dates in int, char(8) & char(10) datatypes. It's painful but I can't change it.:(

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL SERVER ROOKIE Ten Centuries Points: 1032 More actions · Answer 7

SQL SERVER ROOKIE

Ten Centuries

Points: 1032

November 19, 2013 at 8:31 am

#1667435

Same here!!!:rolleyes:

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

We all have those things we can't change and often they are the most frustrating. Hopefully you at least realize how awful of a design that is and don't do it yourself because you have seen just how painful it can be. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

Heh... I remember some of the original computer games. If you can't move left, right, forward, back, up, or down, then change color!

Add a persisted computed column to the table with Luis' formula. If the app was written incorrectly, the app will break and you can just drop the column to "fix" it. If the app doesn't break, then you have the best of both worlds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)