SQL Server Performance

Convert from mm/dd/yyyy to yyyymmdd

Discussion in 'SQL Server 2005 General Developer Questions' started by jbharani, Oct 22, 2007.

  1. jbharani New Member

    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.
  2. patel_mayur New Member

    Please look into BOL for "CONVERT" function and valid parameters you can specify.SELECT
    CONVERT(VARCHAR,GETDATE(),112)
  3. Madhivanan Moderator

    Are you using DATETIME datatype to store dates?
    Where do you want to show converted dates?
  4. jbharani New Member

    I have two tables oldtbl and newtbl.I am trying to populate the newtbl from oldtbl , In Oldtbl i have a column by name Date of type (datetime).... in newtbl i have a column YYYYMMDD of type (int). To populate the newtbl column i need to convert the date.
  5. Madhivanan Moderator

    [quote user="jbharani"]
    I have two tables oldtbl and newtbl.I am trying to populate the newtbl from oldtbl , In Oldtbl i have a column by name Date of type (datetime).... in newtbl i have a column YYYYMMDD of type (int). To populate the newtbl column i need to convert the date.
    [/quote]
    Did you use this?
    SELECT CONVERT(VARCHAR(8),GETDATE(),112)
  6. jbharani New Member

    SELECT CONVERT(VARCHAR(8),GETDATE(),112)
    If i use this the output format will be yymmdd ....
  7. FrankKalis Moderator

    [quote user="jbharani"]
    SELECT CONVERT(VARCHAR(8),GETDATE(),112)
    If i use this the output format will be yymmdd ....
    [/quote]
    Is this a question?
  8. jbharani New Member

    actually Frank i need to convert the datatype from datetime to int... so if i use that i'll get the output in yymmdd format...but i need it in yyyymmdd format...
  9. ndinakar Member

    If you are doing this just for display purpose, you should do it at the front end.
  10. jbharani New Member

    its not just for display purpose...
  11. FrankKalis Moderator

    DECLARE
    @dt DATETIME
    SET @dt = GETDATE()
    SELECT CONVERT(CHAR(8), @dt, 112)--------20071023(
    1 row(s) affected)
  12. jbharani New Member

    Hi Guys,
    I could convert datetime to int ...
    i used this : convert
    (char(10),Date,112)
    O/P: 20030306
  13. FrankKalis Moderator

    CHAR(8) shall also be fine, but it's more important that you resolved your issue anyway. [:)]
  14. Adriaan New Member

    If you choose INT for storing dates, then you're out of luck if you ever need to use functions like DATEPART, DATEADD, DATEDIFF inside SQL Server.
    In case the INT dates are a requirement for some external software, then consider creating a view that casts/converts the datetime column as an INT.
  15. satya Moderator

  16. Madhivanan Moderator

    [quote user="jbharani"]
    Hi Guys,
    I could convert datetime to int ...
    i used this : convert
    (char(10),Date,112)O/P: 20030306
    [/quote]
    Does it mean that you dont follow the replies properly? [;)]
  17. jbharani New Member

    I have used your reply Madhivanan....
  18. jbharani New Member

    Thanks for your reply...

Share This Page