convert from MM/DD/YYYY to YYYYMMDD

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

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

    😎

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

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

    😎

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

  • hi folks,

    I solved this issue...

    i used this to convert from datetime to int datatype

    convert(char(10),Date,112)

    O/P:20030306

  • 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]

  • 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

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

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

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

  • Don't convert nor update!

    Create a calculated column with the new datetimeformat...


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

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

  • 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

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

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

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

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

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