Datetime : Datepart - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Datetime : Datepart

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-06-11 : 03:59:02

INSERT INTO [IMPAPT].[dbo].[YearMaster]
([yearID]
,[year]
,[IsActive]
,[creationDate])
VALUES
(1
,datepart(yy,getdate()) --------line need to be discuused
,1
,getdate())
the output result is
yearID Year IsActive creationDate
1 7/2/1905 12:00:00 AM True 6/11/2008 1:21:17 PM

But when i write query

select datepart(yy,getdate() As Year
it throws result : 2008
Why this is not happenin in the insert query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 04:03:17

Because [YEAR] column is not smallint. It is datetime.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 04:06:35

Which means you inserted a date 2008 days later than 19000101, which by coincidence is '7/2/1905'

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

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-06-11 : 04:10:09

First ,I have mentioned Year as Datetime.
Second ,you mean to say datepart(yy,getdate() gives you small int ? If not then i have declared the 'year' as datetime and it shoul take the correct year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 04:14:05

quote:
Originally posted by pradeep_iete

First ,I have mentioned Year as Datetime.
Second ,you mean to say datepart(yy,getdate() gives you small int ? If not then i have declared the 'year' as datetime and it shoul take the correct year.

Try casting datepart(yy,getdate()) to varchar before inserting to Year column

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 04:16:21

OMG...
If you want the YEAR column to be DATETIME, do you then want the DATE inserted to be '1/1/2008', '1/1/2007' and similar?

replace
,datepart(yy,getdate()) --------line need to be discuused

with
,dateadd(year, DATEDIFF(YEAR, 0, getdate()), 0) --------line need to be discuused

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 07:49:34

quote:
Message from pradeep_iete

If do not mind could you explain

OMG...

DATEPART function returns a NUMBER.
For today, the DATEPART(YEAR, GETDATE()) function returns the number 2008, which is the year today. Right?
This is a number, not a date. Please remember that.

When you later insert the number 2008 to a DATETIME datatype, Microsoft SQL Server treats the number 2008 as an offset to January 1st 1900. If you add 2008 days (still a number) to the date January 1st 1900 you will get the date July 2nd 1905.

You can resolve this is one of two ways.

1) Do as I suggested at 06/11/2008 : 04:16:21 and then you always insert a date (January 1st) for the current year
2) Change YEAR column to smallint and keep the insert statement as is. Then you insert a number which equals the current year.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources