Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Converting Excel Date to sql Date Format Expand / Collapse
Author
Message
Posted Friday, May 27, 2011 1:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
Points: 22, Visits: 106
My problem is kind of weird

In our project we mainly write data from excel to sql-server tables

The date in the excel sheet is in excel's propriety date format i.e 40630.38042 which is equivalent to 03/28/2011 xx:xx:xx(some date time value)

Now the problem is that we were using the code to convert this decimal date value to varchar value 03/28/2011 using this formula

CONVERT([varchar](10), dateadd(day,-2,convert(decimal,[orderdate])),(101))

orderdate here is in this format - 40630.38042

Now after 6 months of uploading hundreds of excel sheets we realized that the dates were not getting converted right

the time part after the decimal place was messing up this logic - and we don't even need time in our database

so I tried CONVERT([varchar](10), dateadd(day,-2,convert(decimal,substring([orderdate],1,5))),(101))
and it seems to work fine

Now to apply the correct formula I first need to change them back to the original decimal date format

so basically I need a reverse formula for
CONVERT([varchar](10), dateadd(day,-2,convert(decimal,[orderdate])),(101))

Is there a way to do this??

If not I'll have to upload all the data (100+ excel sheets again) - which will not make my clients very happy

Thanks,
Kavita
Post #1116041
Posted Friday, May 27, 2011 4:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 20, 2014 1:22 PM
Points: 548, Visits: 1,010
Can you provide an example to work with?

i.e.
a) Value in Excel format
b) Date value you expected
c) Incorrect Date value currently in SQL
Post #1116109
Posted Friday, May 27, 2011 4:38 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
select CONVERT(numeric(18,4),getdate(),101);

Update Table set datecolumn = CONVERT(numeric(18,4),datecolumn,101);


Note

First take backup your database and test it on sample data first then come to production Server


Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com
Post #1116117
Posted Friday, May 27, 2011 4:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
Points: 22, Visits: 106
Hi Shark,

Declare @OrderDate varchar(100)

--Original DateTime from Excel
set @OrderDate = '40441.50551'

select CONVERT([varchar](10), dateadd(day,-2,convert(decimal,@orderdate)),(101))
select CONVERT([varchar](10), dateadd(day,-2,convert(decimal,substring(@orderdate,1,5))),(101))

Results
09/21/2010 --Incorrect
09/20/2010 --Correct
Post #1116120
Posted Friday, May 27, 2011 4:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 20, 2014 1:22 PM
Points: 548, Visits: 1,010
Seems to have rounded them all up by 1 day due to the time on the end? (some are probably ok though?) (confirm this)

If so it may be safer to just do a comparison against the date column and the correct calc to see which ones are wrong, then just update them with a dateadd -1 d (I can provide this if you can confirm the first question)

I tested the code provided above by Syed but had issues getting it to convert back and if it did it came out as 0000.0000?
Post #1116127
Posted Friday, May 27, 2011 5:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
Points: 22, Visits: 106
Shark,

You are absolutely right. Some Values are converted correctly and some aren't.
Post #1116132
Posted Friday, May 27, 2011 5:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
Points: 22, Visits: 106
And Yes the difference is always of 1 day
Post #1116133
Posted Friday, May 27, 2011 5:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 20, 2014 1:22 PM
Points: 548, Visits: 1,010
Just realised my method is no good as you can't work out which ones were wrong in the first place without reloading your sheets!

Will get back to you.
Post #1116149
Posted Friday, May 27, 2011 5:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 20, 2014 1:22 PM
Points: 548, Visits: 1,010
Whats the impact on business reporting of the order date being out 1 day? Some monthly reports would show up orders that shouldn't be there and should be in previous month...thats it though?

From a practical level I'm wondering of the benefit of trying to fix an issue that has left "order date" out by 1 day on just some orders.


EDIT: And to clarify - you have an impossible task getting them back and then converting again because in storing the value to begin with you have lost the time part of the excel format therefore you can't get it back to the value it originally was, you therefore cannot then apply that through the correct conversion to get the value you need...

Run this to see what I mean

Declare @OrderDate varchar(100)
, @Date1 datetime

SELECT '40441.50551' AS OriginalExcelDate
set @OrderDate = '40441.50551'

set @date1 = (select CONVERT(datetime,CONVERT([varchar](10), dateadd(day,-2,convert(decimal,@orderdate)),101),101))
select @date1 AS OriginalSQLValue

-- Retrieved SQL DateTime in Excel format
set @orderdate = (select CONVERT(numeric(18,4), @Date1, 101))
SELECT @OrderDate AS NewExcelValue

select CONVERT([varchar](10), dateadd(day,-2,convert(decimal,substring(@orderdate,1,5))),(101)) AS NewSQLValue
Post #1116151
Posted Friday, May 27, 2011 6:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 6:31 AM
Points: 22, Visits: 106
Shark,

1 day wont make any difference if the month is the same. Most of our reports are generated on monthly basis so now that I am sure the date is always messed up with by one day, I just need to reload the excel sheets that have data for weeks that overlap between two months like 9/27 - 10/1.

This way I'll have to upload just 20 files instead of 200+ files..

It does make my work a lot easier.

Thanks a lot for your time :)
Post #1116181
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse