How to convert day, month and year into month and year

Question

0.00/5 (No votes)

See more:

Hi Everyone

I have table called MCALLERS on my database that has column which called CallDate, datatype is datetime stored data as 2016-02-01 17:01:00.000

on my web Form I have dropdownlist which is populating month and year as March-2016.

how to convert datetime into month and year when I search data.I want to search data from database using Month and year as (March-2016). I'm sing C#.

Thanks in advance

What I have tried:

SQL

select BusinessCallYN,CallID,NewNam,CallDate,CalledNo,CallExt,CallDuration,Amount,tmpNewNam from MCALLERS where UserName='"+txtEmail+"' and CallDate>='"+cmddropdownlist1+"' and CallDate<'"+cmddropdownlist1+"' and Amount>0 order by CallDate

Error message : Conversion failed when converting date and/or time from character string.

Posted 29-Mar-16 0:44am

Member 12390137

Updated 29-Mar-16 1:14am

Patrice T

v2

Add a Solution

Comments

FARONO 29-Mar-16 6:58am

You need to convert CallDate to "Month-Year" format to make it possible to compare. Example:(SELECT right(convert(varchar, getdate(), 106), 8);

4 solutions

Solution 2

Hi,

You need to review SQL Server CONVERT() Function[^] to understand the DateTime conversion.

Hope above link will help you.

Posted 29-Mar-16 1:06am

Dusara Maulik

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)

F-ES Sitecore · Accepted Answer · 2016-03-29T01:02:00

Solution 1

You can use month and yesr to search on just those parts

SQL

where month(CallDate) = 3 and year(CallDate) = 2016

However you should use parameterised queries rather than string concatenation as your code is liable to sql injection attacks.

Posted 29-Mar-16 1:02am

F-ES Sitecore

Comments

Member 12390137 30-Mar-16 8:18am

Hi F-Es
Hi OriginalGriff
i'm using dropdownlist that populate month and year as (march-2016)

where month(CallDate) = '"+Ddlmonths.selectedValue.ToString()+"' and year(CallDate) = '"+Ddlmonths.selectedValue.ToString()+"'

the above query is giving Error how to search data into database using Dropdownlist value (March-2016) and populate data into Gridview. Please help

F-ES Sitecore 30-Mar-16 9:40am

You don't need aprostrophes for integer values

where month(CallDate) = "+Ddlmonths.selectedValue.ToString()+" and year(CallDate) = "+Ddlmonths.selectedValue.ToString()+"

If that still throws an error post what the error is.

OriginalGriff · Accepted Answer · 2016-03-29T01:07:00

For starters, do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
Secondly, try:

SQL

SELECT * FROM MyTable WHERE DATEPART(year, DateColumn) = yearYouWant AND  DATEPART(month, DaetColumm) = monthYouWant

If you want to use your combobox directly, then you need to identify exactly what datatype the combobox contains, because string and DateTime will require different solutions.

CHill60 · Accepted Answer · 2016-03-29T01:14:00

Another take would be to find dates between the first and last days in the month e.g.

C#

DateTime dt1;
if (!DateTime.TryParse("01-" + cmddropdownlist1.SelectedText, out dt1))
{
    //Report an error
    return;
}
var firstDayNextMonth = dt1.AddMonths(1);
var dt2 = firstDayOfNextMonth.AddDays(-1);

var cmd = new SqlCommand(
    "select BusinessCallYN,CallID,NewNam,CallDate,CalledNo,CallExt,CallDuration,Amount,tmpNewNam from MCALLERS where UserName=@email and CallDate between @dt11 and @dt2 and Amount>0 order by CallDate");
cmd.Parameters.AddWithValue("@dt1", dt1);
cmd.Parameters.AddWithValue("@dt2", dt2);

Note
- I've used command parameterization
- I've use the SQL BETWEEN comparison
- DateTime.TryParse to ensure the date is in the correct format
- I've assumed that column CallDate is a Date or DateTime type

How to convert day, month and year into month and year

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0