convert Date format to MS Excel Text in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

convert Date format to MS Excel Text in SQL

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 09:24:32

When a date like: 20/08/08 is converted into a text format in Microsoft Excel it equals to 39680
To do above conversion the Format Cells.. options is used to convert from Date to Text.

Now is there any way in SQL I can convert the date into that number?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 09:28:02

DATEDIFF(DAY, '19000101', MyDateTimeCol).

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

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 09:53:46

quote:
Originally posted by Peso

DATEDIFF(DAY, '19000101', MyDateTimeCol).

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

Thanks for your reply Peso, just a little bit more help needed on it.

I did :
SELECT DATEDIFF(DAY, '19000101', '20/08/08') to check if it converts into the same number but I guess the DATEDIFF ( datepart , startdate , enddate )
enddate format has to be same as the start date
As i'm getting the folowing error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Is there a function which can convert the enddate into the same format as the start date?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 09:55:23

No, it means you datetime columns are not in the proper format. You have not used DATETIME to store your dates.

DATEDIFF ( day , cast(col1 as datetime), cast(col2 as datetime) )

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

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 10:12:57

quote:
Originally posted by Peso

No, it means you datetime columns are not in the proper format. You have not used DATETIME to store your dates.

DATEDIFF ( day , cast(col1 as datetime), cast(col2 as datetime) )

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

I am not using any columns yet, just trying to match the number when converted in excel, so I tried it as follows:
SELECT DATEDIFF(DAY, '19000101' , '20080808' )

and the answer is: 39666

However, it should be 39680

Any more suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-25 : 10:16:53

What is the logic?
SELECT DATEDIFF(DAY, '18991218' , '20080808' )

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 10:17:08

39680, according to who?

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

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 10:20:14

quote:
Originally posted by Peso

39680, according to who?

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

Quoting my starting requirement:
"When a date like: 20/08/08 is converted into a text format in Microsoft Excel it equals to 39680"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 10:25:49

quote:
Originally posted by madhivanan

What is the logic?
SELECT DATEDIFF(DAY, '18991218' , '20080808' )

Madhivanan

Failing to plan is Planning to fail

Trying to find the logic behind how microsoft excel converts it.

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 10:55:23

quote:

I am not using any columns yet, just trying to match the number when converted in excel, so I tried it as follows:
SELECT DATEDIFF(DAY, '19000101' , '20080808' )

and the answer is: 39666

However, it should be 39680

Any more suggestions?

SELECT DATEDIFF(DAY, '19000101' , '20080820' )

and the result to above is: 39678

However, it should be 39680 when I convert the date(20/08/08) from Date format to the Text format in MS Excel.

I don't know why there is a two day difference, can anyone help further please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-25 : 10:57:57

That is because EXCEL treats 18991230 as day ZERO and SQL Server treats 19000101 as day ZERO.

SELECT DATEDIFF(DAY, '18991230' , '20080820' )

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

umertahir
Posting Yak Master

154 Posts

Posted - 2008-08-25 : 11:02:50

quote:
Originally posted by Peso

That is because EXCEL treats 18991230 as day ZERO and SQL Server treats 19000101 as day ZERO.

SELECT DATEDIFF(DAY, '18991230' , '20080820' )

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

Yes, that is it. cheers

prashantdighe
Starting Member

21 Posts

Posted - 2012-02-17 : 03:03:21

Hey guy some freaking problem is here,

My Excel data is:

17-02-2012

I tried :

INSERT INTO Session_Details (Session_Date) SELECT Convert(SMALLDATETIME,t2.Session_Date,101) FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=C:\inetpub\wwwroot\Feedback\Program_Data\Book4.xlsx', 'Select * from [Sheet1$]') t2

But I get

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

PLEASE GIVE SOME IDEAS!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources