[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

Need only the year from a yyyy-mm-dd hh:mm:ss.000 field

I am importing records from Interbase into a SQL Server staging table.  I need to take a date field in the staging table that is in the format: "yyyy-mm-dd hh:mm:ss.000" and put it in the final table as SmallDateTime field with format of  "yyyy".  I tried using "DatePart(yyyy, DateRequested)" but it ends up in the receiving field as "06/29/1905" when the date coming in is "2005-04-12 01:25:12.111".  I have studied the documentation but I still don't get it.  Thanks for any advice.
  • 3
1 Solution
DatePart(yyyy, DateRequested)
gives an integer value for the year of the date

so.. 2005 in this case.

if you put 2005 into a smalldate, it sees this as 2005 days after the start of its calendar.

try something like this
cast ( cast ( DatePart(yyyy, DateRequested) as varchar) +'-1-1' as SmallDateTime)
You cant just store the "yyyy" in a SmallDateTime field - change that field to varchar and use this:

select datepart(year,convert(datetime,'2005-04-12 01:25:12.111'))
sorry, too quick with my submitting.

the above will give january first 2005 in a small date.
if you need only the year.

DatePart(yyyy, DateRequested)

is enough, but as said. it is an integervalue, not a date.

beechersAuthor Commented:
I did as you suggested and changed my "Year" field to a Varchar(4).  However, I was a bit confused about your solution.  I tried " cast(DatePart(yyyy,DateCreated) AS Varchar(4)) " and that worked.  
reading back I can see what you mean ;)

I assumed you had no control over the fact that the smalldate was the target datatype
so what my solution did, was create a string for january first 2005, then cast that string to a date

glad to be of help.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now