Convert(varchar, convert(date, [date], 103), 101)

Question

1.00/5 (1 vote)

See more:

when i use convert(varchar,convert(date, [Date], 103),101) if data is not available on particular date this query is fetching next date data .

What I have tried:

convert(varchar,convert(date, [Date], 103),101)

Posted 11-Sep-16 20:50pm

Member 12363094

Updated 11-Sep-16 22:50pm

Add a Solution

Comments

Maciej Los 12-Sep-16 3:10am

Really? Could you be so kind to provide example data to proof that your statement is true?

Member 12363094 12-Sep-16 4:34am

yeah really when i have run above query in ssms it is coming well
but when i am running application there i am calling this sp at that time i am getting other date date if data is not on that date.

Member 12363094 12-Sep-16 4:35am

again wen i change stored proc then i am able to download perfectly

Tushar sangani 12-Sep-16 3:31am

heloo yuo use this formate to convert date

convert(varchar(10),[Date],101)

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)

Maciej Los · Answer 1 · 2016-09-11T22:50:00

Member 12363094[^] wrote:
when i use convert(varchar,convert(date, [Date], 103),101) if data is not available on particular date this query is fetching next date data .

Above statement is untrue!

SQL

SELECT convert(varchar,convert(date, null, 103),101) AS myDate
--returns: NULL

SELECT convert(varchar,convert(date, '', 103),101) AS myDate
--returns: 1900-01-01

For further details, please see: CAST and CONVERT (Transact-SQL)[^]

MS wrote:
When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.