how to set date format?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi i am new sql sever 2008 r2. Actually i am having a query like this

SQL

DECLARE @FROM varchar(50),
@TO VArchar(50);
SET @FROM ='-07-01';
SET @TO ='-06-30'
  SELECT     
StudentDATE=convert(Date,Convert(varchar(50),year(StudentDATE)-1)+@FROM),StudentDATE=convert(Date,Convert(varchar(50),year(StudentDATE))+@TO) from Student Group By year(StudentDATE) order by year(StudentDATE)
i am getting output correct way .

but i want to pass months also automatically for that i was wrote query like this

SQL

DECLARE @FROM varchar(50),
@TO VArchar(50);
SET @FROM ='-01';
SET @TO ='-30'

SELECT 

StudentDATE=convert(Date,Convert(varchar(50),year(StudentDATE)-1)+month(StudentDATE)+@FROM),StudentDATE=convert(Date,Convert(varchar(50),year(StudentDATE))+month(StudentDATE)+@TO) from Student Group By year(StudentDATE) order by year(StudentDATE)

I am get error message like this

conversion failed when converting date and /or time to character to string ..
please help me ..

Posted 23-Sep-13 20:46pm

Member 9846414

Updated 23-Sep-13 20:53pm

Amol_B

v2

Add a Solution

Comments

gvprabu 24-Sep-13 8:49am

Hi.... exactly what is your Input ? what u need to compare in Date Time field?....

gvprabu 24-Sep-13 8:51am

Give Some sample data from your table .... then its help full for give your the exact solutions.

1 solution

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)

ArunRajendra · Answer 1 · 2013-09-23T21:09:00

Solution 1

Try this query with your second option

SQL

select convert(Date,convert(varchar(50),cast((year(StudentDATE)-1) as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@FROM)),
convert(Date,convert(varchar(50),cast(year(StudentDATE)as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@TO))

Posted 23-Sep-13 21:09pm

ArunRajendra

Comments

Member 9846414 24-Sep-13 5:41am

thanks for reply i tried this but i am getting an error
at from
any help plase helpme for above one with that only i can sloved my prblm

select StudentDATE= convert(Date,convert(varchar(50),cast((year(StudentDATE)-1) as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@FROM)),
StudentDATE=convert(Date,convert(varchar(50),cast(year(StudentDATE)as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@TO)) FROM STUDENT where STUDENTID='1' And StudentDATE<=convert(Date,convert(varchar(50),cast((year(StudentDATE)-1) as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@FROM)) and convert(Date,convert(varchar(50),StudentDATE>=cast(year(StudentDATE)as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@TO)) group by year(StudentDATE) order by year(StudentDATE)

help me

ArunRajendra 24-Sep-13 5:50am

What's the error you getting?

ArunRajendra 24-Sep-13 5:53am

Try this query I have corrected the error.

select StudentDATE= convert(Date,convert(varchar(50),cast((year(StudentDATE)-1) as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@FROM)),
StudentDATE=convert(Date,convert(varchar(50),cast(year(StudentDATE)as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@TO))
FROM STUDENT where STUDENTID='1' And
StudentDATE<=convert(Date,convert(varchar(50),cast((year(StudentDATE)-1) as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@FROM))
and StudentDATE>=convert(Date,convert(varchar(50),cast(year(StudentDATE)as varchar )+ '-' +cast(month(StudentDATE)as varchar )+@TO))
group by year(StudentDATE) order by year(StudentDATE)

Member 9846414 24-Sep-13 7:56am

hi thanks for helping me .i excute this query but when i am add group by its show error like this

coloumn STUDENT.studentDate is invalid in the select list because it is not contained in either an aggregate function or the group by clauses

help me

ArunRajendra 24-Sep-13 23:58pm

If you are using group by you need to use functions like max, min, etc. Tel me what is your requirement, Why do you want to group it?

Member 9846414 25-Sep-13 7:10am

thanks a lot for helping .

Member 9846414 25-Sep-13 7:34am

can u help for by cliking below link
http://www.codeproject.com/Questions/658749/how-to-remove-Nulls-in-a-coloumn
help me..

ArunRajendra 25-Sep-13 7:39am

If this has helped you mark the question as answered. It will help others as well.