DATETIME insert year only

Posted on 2009-04-22
Last Modified: 2012-05-06
Hi guys,

I'm in Access. Let's say I have a table Test with one field Year in it. Type is DATETIME. I use this SQL statement: INSERT INTO Test VALUES (1977);

What I get in table is 5/30/1905. Why is that? Is there a SQL statement that would insert year only? Or should I use TEXT instead of DATETIME? But then it would allow for any value in that field; I don't want that. I don't want to use VBA either. What are my options here?

And one more simple question: from what I understand there is no way to create a SQL statement in Access that would drop a table only if it exists, is there? DROP TABLE IF EXISTS Test; not working. No VBA as well.

Question by:RealSnaD
    LVL 15

    Accepted Solution

    Store the year as an integer instead of as a date. 1977 is not a date, but computers store dates as a reference number, so your computer thinks you mean the date that translates to 1977.

    As for your other question:

    If Exists(Select [ID} from sysobjects where [name] = "MyTableName" and type = 'u") Drop Table MyTableName
    LVL 7

    Author Comment

    Ok, thank you. I stored year as SMALLINT. If exists still is not working, it says Invalid SQL statement. Expected Delete, Ins, Procedure, Sel, or Upd. I am in query SQL designer. But that's not critical. I was just curious.
    LVL 7

    Author Comment

    By the way, is that a common practice to store year as int in case only year is needed?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    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

    13 Experts available now in Live!

    Get 1:1 Help Now