Learn how to a build a cloud-first strategyRegister Now


DATETIME insert year only

Posted on 2009-04-22
Medium Priority
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:Yury Merezhkov
  • 2
LVL 15

Accepted Solution

MNelson831 earned 2000 total points
ID: 24209914
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

Author Comment

by:Yury Merezhkov
ID: 24210029
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.

Author Comment

by:Yury Merezhkov
ID: 24210034
By the way, is that a common practice to store year as int in case only year is needed?

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

810 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