SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Excel Date to sql Date Format


Converting Excel Date to sql Date Format

Author
Message
KavitaA
KavitaA
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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 Sad

Thanks,
Kavita
Shark Energy
Shark Energy
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1787 Visits: 1018
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
Syed Jahanzaib Bin hassan
Syed Jahanzaib Bin hassan
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2164 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
KavitaA
KavitaA
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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
Shark Energy
Shark Energy
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1787 Visits: 1018
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?
KavitaA
KavitaA
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 106
Shark,

You are absolutely right. Some Values are converted correctly and some aren't.
KavitaA
KavitaA
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 106
And Yes the difference is always of 1 day
Shark Energy
Shark Energy
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1787 Visits: 1018
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.
Shark Energy
Shark Energy
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1787 Visits: 1018
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
KavitaA
KavitaA
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search