datetime problem - default date

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

General SQL Server Forums

New to SQL Server Programming

datetime problem - default date

Author

Topic

veganrobot
Starting Member

6 Posts

Posted - 2011-10-22 : 05:38:49

Hello All,

I have a problem with using datetime type (SQL Server 2008).

This is the code I am using:


DECLARE @testTime AS DATETIME
DECLARE @testDistance AS FLOAT

SET @testTime ='1900-01-01 00:49:30.000'
SET @testDistance = '10.00'

SELECT @testTime AS testTime
	,convert(FLOAT, @testTime) AS timeAsFloat
	,@testDistance AS distance
	,cast(cast(@testTime AS FLOAT)/@testDistance AS DATETIME) as MinPerKM
	,cast(@testDistance / (cast(@testTime AS FLOAT)) AS FLOAT)/24 AS KMperHR

Which outputs (which is correct):


testTime                timeAsFloat            distance               MinPerKM                KMperHR
----------------------- ---------------------- ---------------------- ----------------------- ----------------------
1900-01-01 00:49:30.000 0.034375               10                     1900-01-01 00:04:57.000 12.1212121212121

My problem is that when I upload a CSV file with the data (using the import wizard) TestTime gets uploaded as '1899-12-30 00:49:30.000' this then throws out my calculated fields.

Using

SET @testTime ='1899-12-30 00:49:30.000'

in the above code gives me the below output (which is incorrect):


testTime                timeAsFloat            distance               MinPerKM                KMperHR
----------------------- ---------------------- ---------------------- ----------------------- ----------------------
1899-12-30 00:49:30.000 -1.965625              10                     1899-12-31 19:16:57.000 -0.211976682564918

This is from the CSV file:

10,00:49:30

Is there a way to force SQL server to set the date to 1900-01-01?

If not can someone please suggest another approach to get the correct output?

I have tried using the TIME type for testTime but this then gives me casting errors.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 05:45:41

how are you exporting excel? instead of export import wizard you can use ssis package and do data conversion to add default date to it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

veganrobot
Starting Member

6 Posts

Posted - 2011-10-22 : 06:53:17

The CSV upload file is an Excel workbook saved as CSV. I dont think it is a formatting issue.

Apart from SSIS is there another solution to the problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 06:57:02

i never told its a formatting issue. i was telling to convert it to proper datetime format using data conversion task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

veganrobot
Starting Member

6 Posts

Posted - 2011-10-22 : 07:09:09

Oh okay.

Sorry you lost me regarding:

"i was telling to convert it to proper datetime format using data conversion task"

Could you please elaborate on this? What data conversion task are you referring to?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 07:49:27

you've a data conversion task inside SSIS. by default it takes all fields from excel as unicode string. i was telling to apply data conversion to change it to datetime.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

veganrobot
Starting Member

6 Posts

Posted - 2011-10-22 : 08:28:55

Now I know what you mean

Yep, I had already ensured that the datetime type was selected.

Anything else I should check?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 09:08:59

still its taking date part as 1899-12-31

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

veganrobot
Starting Member

6 Posts

Posted - 2011-10-23 : 06:50:15

Yes its still uploading as:


1899-12-30 00:49:30.000

I tried uploading a new .txt file created with Notepad as I have read that the 1899-12-30 date is a "bug" from Excel. However this didnt help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 11:08:25

ok can you try adding a derived column in ssis to add one day to fields using DATEADD

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-23 : 14:24:05

Hello vegarno an all people ,I suggest you to use the new datatype called datetime2 ,Why?
36 down vote

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999.

Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.

paul Tech

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 04:25:34

quote:
Originally posted by paultech

Hello vegarno an all people ,I suggest you to use the new datatype called datetime2 ,Why?
36 down vote

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999.

Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.

paul Tech

Even then the problem will persist. the problem here is way in which excel interprets the default base date part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-24 : 09:56:34

create table t1 (c1 varchar(1000))
go
bulk insert t1 from 'c:\temp\test.txt'
select * from t1

c1
--------
10,00:49:30

select
cast(parsename(replace(c1,',','.'),2) as float) as dist
,convert(float,cast(parsename(replace(c1,',','.'),1) as datetime)) as dt
from t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 11:08:35

quote:
Originally posted by DBAPBFL

create table t1 (c1 varchar(1000))
go
bulk insert t1 from 'c:\temp\test.txt'
select * from t1

c1
--------
10,00:49:30

select
cast(parsename(replace(c1,',','.'),2) as float) as dist
,convert(float,cast(parsename(replace(c1,',','.'),1) as datetime)) as dt
from t1

will work as long as file has maximum of 5 fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-10-24 : 14:11:50

Maybe I am missing something here - but why not use TIME data type instead of worrying about datetime? It appears the data that is being sent is only the time portion.

That would/should eliminate any issue with the date.

Oh - just saw that you had conversion errors. Maybe posting the code and conversion errors here and we might be able to fix those issues and allow you to use the right data type.

veganrobot
Starting Member

6 Posts

Posted - 2011-10-24 : 23:59:14

Hey All,

Thanks for your help.

I couldnt figure it out so I just hardcoded the base date into the upload file. (didnt do in SSIS as I have only just started to play around with SSIS)

I did try the TIME datatype but I get the following error:
Explicit conversion from data type time to float is not allowed.

I need the time value to be converted into a FLOAT so that I can then use it in a formula to calculate the minutes per KM. (i.e. time/distance)

I am only new to SQL but to me I would have assumed that this would have been a simple task.

More for curiosity purposes does anyone know if in theory I should have been able to do a simple upload and SQL uses the 1901-01-01 default base date?

Once again thank you for your input

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources