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

Posted on 2005-04-21
Last Modified: 2012-08-14
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.
Question by:beechers
    LVL 11

    Expert Comment

    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)
    LVL 2

    Expert Comment

    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'))
    LVL 11

    Accepted Solution

    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.


    Author Comment

    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.  
    LVL 11

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now