Convert datetime - SQL Server Forums

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

Convert datetime

Author

Topic

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 05:08:35

can anyone help with the following code

I am writing with limited access to a database originally i used a predefined function as follows

USE [FMData]
GO
/****** Object: UserDefinedFunction [dbo].[GetFMDate] Script Date: 07/21/2008 09:51:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetFMDate] (@Input_Date as int) RETURNS DateTime

BEGIN

DECLARE @Output as DateTime

IF @Input_Date <> 0 AND @Input_Date>700000

BEGIN

SET @Output = CONVERT(DATETIME, @Input_Date - 693596)

END

ELSE

BEGIN

SET @Output = NULL

END

RETURN @Output

END

however i now dont have access to this function and have been told to use

CONVERT(DATETIME, dbo.PROGRESS.Scheduled_to_Start - 693596) AS Date

however this is comming up with the dreaded arithmetic overflow error
the original data looks like this

733240

can you help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 05:12:37

what date does 733240 represent ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-21 : 05:18:08

What does the function do?

Madhivanan

Failing to plan is Planning to fail

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 05:23:48

I beleive when it was working it would look like this

17/07/2008 09:17

Regards

Damian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-21 : 05:32:18

In the function, change the following

SET @Output = CONVERT(DATETIME, @Input_Date - 693596)

to

SET @Output = DATEADD(day, @Input_Date - 693596,0)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 05:33:53

Makes no sense.
Please post the integer values and their corresponding human readable formats.

See this topic for better understanding
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88250

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

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 06:16:27

I wish i could post the values you want but without access to that function i cannot get anything to work so im stuck right royally

the only way i can get you something is to interpret the proprietry software.

i get this

733245 18/07/08
733247 21/07/08
733251 22/07/08

I beleive that these will all be expressed with a 00:00 time on the end due to a fault in the software not making all times to 0

Regards

Damian

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 06:34:51

Seems odd.
The first two records have an absolute difference of 2, but the dates are 3 days off.
And then record 2 and 3 has an absolute difference of 4, but days are only 1 day off.

Are you REALLY REALLY sure the integer values and the dates correlate?

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

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 06:36:00

Peso

Bear with me i will try to get you more accurate values

D

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 06:49:42

Try these
733245 22/07/2008
733226 03/07/2008
733247 24/07/2008
733289 04/09/2008
733273 19/08/2008
733276 22/08/2008

I have pulled these from the sales ledger where one order number is delivering one individual part at one delivery date

D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 06:52:24

Is there another column that has time part stored?

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

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 06:56:43

sorry my fault

733245 22/07/2008 00:00
733226 03/07/2008 00:00
733247 24/07/2008 00:00
733289 04/09/2008 00:00
733273 19/08/2008 00:00
733276 22/08/2008 00:00

when i interpret the software it doesnt display time

d

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 06:57:58

Try

SELECT DATEADD(DAY, dbo.PROGRESS.Scheduled_to_Start  - 693596, '19000101') AS Date

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 07:02:12

[code]DECLARE @Sample TABLE (v INT, d DATETIME)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 733226, '03/07/2008' UNION ALL
SELECT 733245, '22/07/2008' UNION ALL
SELECT 733247, '24/07/2008' UNION ALL
SELECT 733273, '19/08/2008' UNION ALL
SELECT 733276, '22/08/2008' UNION ALL
SELECT 733289, '04/09/2008'

SELECT *,
DATEADD(DAY, v - 693596, '19000101') AS Peso
FROM @Sample[/code]The "date" value of 693596 represent the date 3799-01-01 for some reason.
You are 1899 years off! It seems like a prior conversion went wrong.

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

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 07:32:54

when i try the above i get the error
adding a value to datetime column caused overflow

d

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 07:40:45

when i try the above i get the error
adding a value to datetime column caused overflow

d

Damian
Starting Member

12 Posts

Posted - 2008-07-21 : 07:46:19

the original code is

CONVERT(DATETIME, dbo.PROGRESS.Scheduled_to_Start - 693596) AS Tmp1

this fails i think because the data

dbo.PROGRESS.Scheduled_to_Start

is in type float and not a true intiger

however when i try to convert it it doesnt work either

CONVERT(DATETIME,(CONVERT(int, dbo.PROGRESS.Scheduled_to_Start) - 693596) AS Tmp1

I get parsing errors and invalid syntax

Hmm

d

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 07:46:38

you mean running Peso's code as it is or against your database ?

"adding a value to datetime column caused overflow"
Check for any record with value less than 693596

or use CASE WHEN to only convert if value is >= 693596


SELECT CASE WHEN dbo.PROGRESS.Scheduled_to_Start>= 693596
            THEN DATEADD(DAY, dbo.PROGRESS.Scheduled_to_Start  - 693596, '19000101') 
            ELSE NULL
            END AS Date

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 07:48:54

quote:
Originally posted by Damian

the original code is

CONVERT(DATETIME, dbo.PROGRESS.Scheduled_to_Start - 693596) AS Tmp1

this fails i think because the data

dbo.PROGRESS.Scheduled_to_Start

is in type float and not a true intiger

however when i try to convert it it doesnt work either

CONVERT(DATETIME,(CONVERT(int, dbo.PROGRESS.Scheduled_to_Start) - 693596) AS Tmp1

I get parsing errors and invalid syntax

Hmm

d