Confused about using SQL data type

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I want to store month(string) and year(numeric) in Sql database Eg : January 2013. Is there any predefined data type available in sql server. If not how can I achieve this? Thank you.

Posted 4-Jun-15 21:39pm

partha143

Add a Solution

5 solutions

Solution 4

there is no datatype in sql that can store single record with multiple data types in a single column you have to use varchar or other string storage data types for your desired data (January 2013)

Solution: save month in separate column (varchar) and year in separate column (numeric)

Posted 4-Jun-15 21:59pm

kashif Atiq

Updated 4-Jun-15 22:00pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2015-06-04T21:55:00

Solution 2

You should store the date/time value into a field with one of these sql date type based on your requirement: Date and Time Data Types and Functions (Transact-SQL)[^]
These date values can then be retrieved and formatted using Custom Date and Time Format Strings[^] for presentation using code like c# or vb.net.

Posted 4-Jun-15 21:55pm

Peter Leow

Comments

partha143 5-Jun-15 4:53am

Thank you Sir. I have now solved my problem.

Peter Leow 5-Jun-15 5:03am

You are welcome.

OriginalGriff · Accepted Answer · 2015-06-04T21:55:00

Solution 1

Don't.
Instead, look at using a DATETIME (or even a DATE) datatype instead. Neither of them store a formatted date, but they store the dat in a format it can be used for a lot more than just presentation.
Store it as the first of the month, in a DATETIME, at midnight, and convert it to the appropriate string for presentation in your presentation software (which is normally pretty trivial). That way, it works seamlessly regardless of which language the user wants to display, and you can do comparisons and groupings a lot more easily. (You also don't have to cope with potential corrupt dates in your DB like "2013 January" or "Junuary 2013" and so on - which can be a nightmare to fix later).

Posted 4-Jun-15 21:55pm

OriginalGriff

Comments

Peter Leow 5-Jun-15 4:02am

Your answer is not only faster but also better.

partha143 5-Jun-15 4:52am

Thank you Sir. As you suggested I used DATE datatype and I solved my problem by converting month number to month name. I just needed another information on saving date for fields such as Academic Year which value will be like "2013-14". Thank you.

OriginalGriff 5-Jun-15 5:09am

That's more complicated, because it depends on what it's going to be used for. Almost certainly, I'd store at as a "starts in" rather than as a year period - and probably use a DATE again, storing the actual day the term starts. You can very easily extract the year portion from that, and generate the academic year as a string if you need it, but the actual start day probably needs to be stored somewhere anyway!

If you are going to store this with every record though, I'd use a second table:
ID (INTEGER, IDENTITY, PK)
TermStart (DATE)
AcademicYears NVARCHAR(7)
and use a foreign key to link that into my main table.
That way it keeps the size of each record down, and is almost trivial to use via an SQL JOIN when you need presentation information.

F-ES Sitecore · Accepted Answer · 2015-06-04T21:57:00

Solution 3

Use Date with a convention that you leave the day as 1 so January 2013 is stored as 1 Jan 2013. Any code or SQL that uses this variable has to simply ignores the day part.

Posted 4-Jun-15 21:57pm

F-ES Sitecore

DamithSL · Accepted Answer · 2015-06-04T22:01:00

Solution 5

you can use Date[^] column type. then you can insert directly Date from your .net application ( use default date like 1st day of month) and when you display the year/month from database you can use format strings like datefromDb.ToString("MMMM yyyy")

Posted 4-Jun-15 22:01pm

DamithSL