convert from MM/DD/YYYY to YYYYMMDD

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » convert from MM/DD/YYYY to YYYYMMDD

29 posts, Page 1 of 3

1 2 3 »»»

convert from MM/DD/YYYY to YYYYMMDD

Rate Topic

Display Mode

Topic Options

Author

Message

tweety

Posted Monday, October 22, 2007 4:06 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 118, Visits: 403

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.

Post #413605

Lynn Pettis

Posted Monday, October 22, 2007 4:29 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 21,832, Visits: 27,850

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #413613

tweety

Posted Tuesday, October 23, 2007 8:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 118, Visits: 403

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

Post #413965

Lynn Pettis

Posted Tuesday, October 23, 2007 9:21 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 21,832, Visits: 27,850

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #413986

tweety

Posted Tuesday, October 23, 2007 9:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 118, Visits: 403

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

Post #413994

tweety

Posted Tuesday, October 23, 2007 2:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 118, Visits: 403

hi folks,

I solved this issue...
i used this to convert from datetime to int datatype

convert(char(10),Date,112)
O/P:20030306

Post #414098

Luke L

Posted Wednesday, October 24, 2007 6:32 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:24 PM
Points: 2,892, Visits: 5,871

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

For better help with performance problems please read this

Post #414377

Amit kulkarni-491236

Posted Thursday, October 25, 2007 2:42 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 24, 2011 9:55 AM
Points: 524, Visits: 219

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

Post #414828