convert from MM/DD/YYYY to YYYYMMDD

Question

Post reply

convert from MM/DD/YYYY to YYYYMMDD

tweety

Mr or Mrs. 500

Points: 564
More actions
October 22, 2007 at 4:06 pm

#179521

Hi friends,
I have a Date column as MM/DD/YYYY(datetime) i need to convert this to YYYYMMDD(int). Can anyone help me in converting this....
i tried this, but am getting an error
SELECT distinct convert(datetime,Date)as [YYYYMMDD]
error:Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
Thanks,
baru.

Viewing 15 posts - 1 through 15 (of 28 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can we ask why you need to do this?

Also, this should work: select year(getdate()) * 10000 + month(getdate()) * 100 + day(getdate())

Replace getdate() with your column name.

😎

tweety Mr or Mrs. 500 Points: 564 More actions · Answer 2

i need to do this coz i have the Date column type as datetime and i need to use this column to populate a column in newtable which has int datatype as the data type and format YYYYMMDD....

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Hopefully the code I provided works for you, but I (and I am sure others will agree) think it is not a good idea to store dates as integer values. Dates should be stored as dates.

😎

tweety Mr or Mrs. 500 Points: 564 More actions · Answer 4

tweety

Mr or Mrs. 500

Points: 564

October 23, 2007 at 9:32 am

#744601

ya, but my team want that to be an int value...

tweety Mr or Mrs. 500 Points: 564 More actions · Answer 5

hi folks,

I solved this issue...

i used this to convert from datetime to int datatype

convert(char(10),Date,112)

O/P:20030306

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

bharani (10/23/2007)
ya, but my team want that to be an int value...

I don't mean to be rude, but... Dates should be stored as dates, soring them as ints will cause you all manner of problems down the road. If you're the DBA and the rest of the team are developers, stand up for your data and let them know that dates are already stored as number, the number of days/hours/seconds/etc from some date depending on the dbms.

Let them reformat it however they want in the business or presentation layer, not at the data layer.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 7

you can use date Style numbers to convert to different date formats

here is an example for converting current date to different formats

select CONVERT( varchar(10), getdate(), 111 )

or

select CONVERT( varchar(10), getdate(), 112 )

where 111,112 are different data style number.

Regards,

Amit kulkarni

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 8

How to convert mm/dd/yyyy date format to yyyymmdd -

try this!!!!!!!!!!

http://www.pcreview.co.uk/forums/thread-1780991.php

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Don't convert nor update!

Create a calculated column with the new datetimeformat...

N 56°04'39.16"
E 12°55'05.25"

Anders Pedersen SSChampion Points: 11410 More actions · Answer 10

You must have Cobol developers on your staff! Man we get that stuff all over the place around here, but I refuse to let them store it in that format when they use SQL, so I have functions that will convert both ways for their applications.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 11

For what it's worth, I just want to add my voice to agree with the others. There are too many ways (user functions, calculated columns) to give your users what they want without bastardizing your data. It may seem like a small issue -- why not give in this time? -- but the Give-An-Inch rule applies: who knows what they'll ask you to do to your data next time and you won't have any precedent with which to argue against it.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

bharani (10/23/2007)
ya, but my team want that to be an int value...

Your team is absolutely wrong... Peter Larson has a good viable solution... storing any date or time as other than a DATETIME datatype will always lead to future problems... always. Your team is trying to commit suicide by falling on the SQL sword.

--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)

Gabika SSC Journeyman Points: 79 More actions · Answer 13

Yep this works. It also works in the DTS query to pull the data. But it should be

CONVERT(char(8), DateColumn, 112) instead of char(10) yyyymmdd is 8 chars.

Srdjan Svrdlan SSC Veteran Points: 204 More actions · Answer 14

He could use VARCHAR instead of char....that way it doesn't matter what value he puts, as long as it is >= 8