Format date - 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

Format date

Author

Topic

srisha
Starting Member

38 Posts

Posted - 2013-06-06 : 02:32:20

select year(POSTDATE) from OEINVH
Postdate column is in date datatype.

Error
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

SRISHA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 02:36:56

Sorry I dont think so. If postdate is of date datatype and if you're using above query i dont think it will cause any overflow as it doesnt require any conversion to datetime again
My guess is you're using another query over this to make it back to datetime which is where overflow occurs due to some unexpected values.
Can you post the full query please?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 02:40:31

Try this one... let us know the output
SELECT convert(varchar(4),POSTDATE,112)
FROM OEINVH

--
Chandu

srisha
Starting Member

38 Posts

Posted - 2013-06-06 : 02:48:38

Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.

Same error comes up

SRISHA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 02:51:41

quote:
Originally posted by srisha

Msg 8115, Level 16, State 5, Line 3
Arithmetic overflow error converting numeric to data type varchar.

Same error comes up

SRISHA

are you sure POSTDATE is date?

check it first


SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'OEINVH'
AND COLUMN_NAME = 'POSTDATE'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

srisha
Starting Member

38 Posts

Posted - 2013-06-06 : 02:57:49

Sorry it is in decimal data type

--------------------------
BY
SRISHA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 03:03:05

ok. that explains.
so whats the value contained in it? is it full date in YYYYMMDD fomat?
if yes ,use


SELECT YEAR(CAST(POSTDATE AS date)) FROM OEINVH

if it just contains a decimal value indicating days

use


SELECT YEAR(DATEADD(dd,POSTDATE,0)) FROM OEINVH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 03:03:48

quote:
Originally posted by srisha

Sorry it is in decimal data type
--------------------------
BY
SRISHA

then in which format date is stored?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 03:03:48

This also explains why its important that you use proper datatype for your fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

srisha
Starting Member

38 Posts

Posted - 2013-06-06 : 03:07:07

yes,it is stored in yyyymmdd format.

If i use first query ,it shows the following error msg

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type decimal to date is not allowed.

--------------------------
BY
SRISHA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 03:10:00

quote:
Originally posted by srisha

yes,it is stored in yyyymmdd format.

If i use first query ,it shows the following error msg

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type decimal to date is not allowed.

--------------------------
BY
SRISHA


SELECT YEAR(CAST(POSTDATE AS datetime)) FROM OEINVH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

srisha
Starting Member

38 Posts

Posted - 2013-06-06 : 03:12:44

when i using above query following error arrives,

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

--------------------------
BY
SRISHA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 03:15:26

quote:
Originally posted by srisha

when i using above query following error arrives,

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

--------------------------
BY
SRISHA

then i guess you've some spurious values

what does this return?


SELECT POSTDATE FROM OEINVH
WHERE ISDATE(POSTDATE) = 0
OR LEN(POSTDATE) < 8

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 03:20:39

hi srisha,
-- see this illustration
DECLARE @yourDate DECIMAL (8)
SET @yourDate = 20130606
SELECT LEFT(@yourDate, 4) -- 2013

--
Chandu

srisha
Starting Member

38 Posts

Posted - 2013-06-06 : 03:21:07

SELECT POSTDATE FROM OEINVH
WHERE ISDATE(POSTDATE) = 1
and LEN(POSTDATE) < 10

it working when i use above coding .
i need only year part from the value

--------------------------
BY
SRISHA

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 03:24:06

quote:
Originally posted by srisha

SELECT LEFT(POSTDATE, 4) FROM OEINVH
WHERE ISDATE(POSTDATE) = 1
and LEN(POSTDATE) < 10

it working when i use above coding .
i need only year part from the value

--------------------------
BY
SRISHA

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 04:00:36

quote:
Originally posted by srisha

SELECT POSTDATE FROM OEINVH
WHERE ISDATE(POSTDATE) = 1
and LEN(POSTDATE) < 10

it working when i use above coding .
i need only year part from the value

--------------------------
BY
SRISHA

why 10?
do you've other date formats also available?
ALso LEN(POSTDATE) < 10 has a potential problem that it may return incomplete date values too which will still return 1 for ISDATE. But if you're only concerned about year part then you're good to go with this

http://visakhm.blogspot.com/2013/05/enforcing-effective-data-validation.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources