none
Hi Experts

    Question

  • How to convert a number 41330 into dateformat yyyymmdd (20130311)??

    Any insights would be helpful!

    Thursday, August 07, 2014 4:56 PM

Answers

  • I am not sure but check if this works:

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,CAST(InvoiceDate AS INT),'1900-01-01')),112)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by Maash Thursday, August 07, 2014 7:49 PM
    Thursday, August 07, 2014 6:41 PM
  • Hi Guys,

    I made it work!! The following was the query that worked.

    SELECT CONVERT(varchar(10),dateadd(day, 12,dateadd(day,CAST(INVOICE_DATE AS INT),'1900-01-01')),112)
    FROM FFLEGInvoiceTradeDetails 
    WHERE len ([INVOICE_DATE])<>8

    UPDATE FFLEGInvoiceTradeDetails
    SET INVOICE_DATE = CONVERT(varchar(10),dateadd(day, 12,dateadd(day,CAST(INVOICE_DATE AS INT),'1900-01-01')),112)
    WHERE len ([INVOICE_DATE])<>8

    Thanks to all for your help! :)


    Maash

    • Marked as answer by Maash Thursday, August 07, 2014 7:38 PM
    Thursday, August 07, 2014 7:38 PM
  • Its great Maash!

    Jingyang Li is the one who suggested you this : convert(varchar(10),dateadd(day, 12,dateadd(day,41330,'1900-01-01')),112)

    This almost resolved your question. It would be nice, if you could mark his reply as answer. :-)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, August 07, 2014 7:44 PM

All replies

  • Use the DATEADD function:

    SELECT DATEDIFF(DD, '19000101', '20130311');
    -- 41342


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Thursday, August 07, 2014 5:04 PM
    Moderator
  • select convert(varchar(10),dateadd(day, 12,dateadd(day,41330,'1900-01-01')),112)
    
    --20130311

    • Proposed as answer by Itz Shailesh Thursday, August 07, 2014 5:10 PM
    Thursday, August 07, 2014 5:06 PM
    Moderator
  • Hi,

    I have a date field which has values like 41330, 41337, 41342 etc. I want to change them to yyyymmdd format like we convert in excel. We can convert from number to date in excel right, similarly is there a way to convert the above mentioned values to their corresponding dates in yyyymmdd format in sql?? 

    I have a huge data out of which around 500 values are like 41330, etc. 

    Thanks in Advance!


    Maash

    Thursday, August 07, 2014 5:20 PM
  • Hi Itz Shailesh,

    It worked for that particular record. I have around 503 records with different values that needs to be converted. Is there a query to convert all the 503 records to the correct format?

    Thanks in Advance!


    Maash


    • Edited by Maash Thursday, August 07, 2014 5:29 PM
    Thursday, August 07, 2014 5:28 PM
  • select convert(varchar(10),dateadd(day, 12,dateadd(day,41330,'1900-01-01')),112)
    
    --20130311

    Hi Itz Shailesh,

    It worked for that particular record. I have around 503 records with different values that needs to be converted. Is there a query to convert all the 503 records to the correct format?

    Thanks in Advance!



    Maash

    Thursday, August 07, 2014 5:30 PM
  • Where do you have the records (rows) ? A table? Which is the name of the table?

    I guess that values are in a specific field (column). Which is the name of the column?

    SELECT CONVERT(varchar(10),dateadd(day, 12,dateadd(day,TheNameOfTheMysteriousColumn,'1900-01-01')),112)
    FROM TheNameOfTheMysteriousTable
    


    EntityLite: A Lightweight, Database First, Micro ORM

    Thursday, August 07, 2014 5:43 PM
  • Yes those values are in specific column called the InvoiceDate. 

    How to convert the above query into an update statement?

    I am sorry if I am not clear in framing my questions. I am a novice in sql! I am still learning. -_-

    Thanks!


    Maash

    Thursday, August 07, 2014 5:52 PM
  • Try This:

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,InvoiceDate,'1900-01-01')),112)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, August 07, 2014 5:58 PM
  • Try This:

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,InvoiceDate,'1900-01-01')),112)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Hi I tried to use this query, but ended up getting the following error.

    Msg 8116, Level 16, State 1, Line 1
    Argument data type varchar is invalid for argument 2 of dateadd function.

    :(


    Maash

    Thursday, August 07, 2014 6:08 PM
  • Before doing the update take the table backup

    Also, try to execute the select statement first

    select 
     CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,InvoiceDate,'1900-01-01')),112)
     from tablename
    where ISNUMERIC(InvoiceDate)=1

    Can you try the below query?

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,InvoiceDate,'1900-01-01')),112)
    where ISNUMERIC(InvoiceDate)=1

    --Prashanth


    Thursday, August 07, 2014 6:24 PM
  • You have this error because InvoiceDate is of varchar datatype. Lets first convert it to INT and try updating it.

    This should work:

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,CAST(InvoiceDate AS INT),'1900-01-01')),112)
    WHERE ISNUMERIC(InvoiceDate) = 1


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".


    Thursday, August 07, 2014 6:26 PM
  • Can you provide your table DDL? Thanks.
    Thursday, August 07, 2014 6:26 PM
    Moderator
  • Hi ItzShailesh,

    Msg 517, Level 16, State 1, Line 1
    Adding a value to a 'datetime' column caused an overflow.
    The statement has been terminated.

    This was the error that popped up after using this query.


    Maash

    Thursday, August 07, 2014 6:32 PM
  • Can you confirm, what is the dataype of the field InvoiceDate?

    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, August 07, 2014 6:36 PM
  • Invoicedate is Varchar

    Maash

    Thursday, August 07, 2014 6:37 PM
  • Hi Prashant,

    Msg 8116, Level 16, State 1, Line 1
    Argument data type varchar is invalid for argument 2 of dateadd function.

    This error is coming when I execute yuur query.


    Maash

    Thursday, August 07, 2014 6:40 PM
  • Hi Jingyang Li,

    I dont know what my table's DDL is. Or i dont know from where i can take it. :/


    Maash

    Thursday, August 07, 2014 6:41 PM
  • I am not sure but check if this works:

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,CAST(InvoiceDate AS INT),'1900-01-01')),112)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by Maash Thursday, August 07, 2014 7:49 PM
    Thursday, August 07, 2014 6:41 PM
  • Also,

    if I use the following statement 

    UPDATE FFLEGInvoiceTradeDetails
    SET INVOICE_DATE = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,41330,'1900-01-01')),112)

    instead of 

    UPDATE FFLEGInvoiceTradeDetails
    SET INVOICE_DATE = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,INVOICE_DATE,'1900-01-01')),112)

    it works. But only those records with the value 41330 gets updated. Others remain the same. For the second query, the following error pops up.

    Msg 8116, Level 16, State 1, Line 1
    Argument data type varchar is invalid for argument 2 of dateadd function.


    Maash

    Thursday, August 07, 2014 6:46 PM
  • I am not sure but check if this works:

    UPDATE YourTableName
    SET InvoiceDate = CONVERT(VARCHAR(10),DATEADD(DAY, 12,DATEADD(DAY,CAST(InvoiceDate AS INT),'1900-01-01')),112)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Nope. It did not work!

    Msg 517, Level 16, State 1, Line 1
    Adding a value to a 'datetime' column caused an overflow.
    The statement has been terminated.


    Maash

    Thursday, August 07, 2014 6:47 PM
  • There seem to be a value in your invoicedate that is not able to convert into date time. Can you paste the result of following :

    SELECT TOP 10 INVOICE_DATE
    FROM FFLEGInvoiceTradeDetails
    ORDER BY LEN(INVOICE_DATE) DESC


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, August 07, 2014 7:38 PM
  • Hi Guys,

    I made it work!! The following was the query that worked.

    SELECT CONVERT(varchar(10),dateadd(day, 12,dateadd(day,CAST(INVOICE_DATE AS INT),'1900-01-01')),112)
    FROM FFLEGInvoiceTradeDetails 
    WHERE len ([INVOICE_DATE])<>8

    UPDATE FFLEGInvoiceTradeDetails
    SET INVOICE_DATE = CONVERT(varchar(10),dateadd(day, 12,dateadd(day,CAST(INVOICE_DATE AS INT),'1900-01-01')),112)
    WHERE len ([INVOICE_DATE])<>8

    Thanks to all for your help! :)


    Maash

    • Marked as answer by Maash Thursday, August 07, 2014 7:38 PM
    Thursday, August 07, 2014 7:38 PM
  • Hi Jingyang Li,

    I dont know what my table's DDL is. Or i dont know from where i can take it. :/


    Maash

    From SSMS, you right click on your table name and script it to a new window and copy the code and post it here. It is very simple but it will others to understand your question.

    Thanks.

    Thursday, August 07, 2014 7:42 PM
    Moderator
  • Its great Maash!

    Jingyang Li is the one who suggested you this : convert(varchar(10),dateadd(day, 12,dateadd(day,41330,'1900-01-01')),112)

    This almost resolved your question. It would be nice, if you could mark his reply as answer. :-)


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Thursday, August 07, 2014 7:44 PM
  • Hi I would totally do it but I cannot see where Jingyang Li has posted this query Itz Shailesh. Can I mark yours as the answer?

    Maash

    Thursday, August 07, 2014 7:48 PM
  • Thank you so much Jingyang Li  and Itz Shailesh for all the help! :D

    Maash

    Thursday, August 07, 2014 7:52 PM
  • You are welcome.

    Important thing is to use a correct data type for your data. Since you are going through a clean up conversion, you should use DATE or DTAETIME data type to store your cleaned date data. Try to use correct data type consistently will help you a lot down the road to write queries.

    Thursday, August 07, 2014 7:56 PM
    Moderator
  • Sure. Thanks for the information Jingyang Li

    I will keep that in mind goind forward! :)


    Maash

    Thursday, August 07, 2014 8:39 PM