How to convert String to Date in Sql server 2008

Question

1.00/5 (1 vote)

See more:

Hi Guys, I have a datagrid .

VB

Dim strQuery As String
            strQuery = "SELECT BENCHMARKNO 'BMK NO',BENCHMARKNAME 'BMK Name',CLIENTCODE 'Client ID',NOFILES 'No.of Files',REFERENCENO 'Reference No',NORESOURCES 'Resources',REPLACE(CONVERT(VARCHAR,STARTDATE,106),' ','-') AS 'Start Date', REPLACE(CONVERT(VARCHAR,FINISHDATE,106),' ','-')  AS  'Finish Date', AUTHORIZEDBY 'Authorized By',JOBNO 'Converted To Job No',REMARKS 'Remarks' FROM QMS_BENCHMARK ORDER BY BENCHMARKNO"
            Dim pBMKTrack As DataTable = GetTable(strQuery)
            dGridview.DataSource = pBMKTrack

in that query , start date and finish date are columns.

if i can tried to sort the column. it doesn't sorted.

I got the dates like these

example : 01-jan-2012

03-Feb-2010
12-aug-2013

I want : 03-Feb-2010
01-jan-2012
12-aug-2013

total date format should be changed.

In DataBase STARTTIME AND FINISHTIME COLUMNS DATATYPE ARE DATETIME.

Please help me ....

Posted 22-Mar-13 4:22am

CH Guravaiah

Add a Solution

Comments

[no name] 22-Mar-13 10:33am

Maybe you could rephrase this. The title of your question, the query and the narrative all discuss 3 different things that appear to have nothing to do with each other. If StartTime and EndTime are already datetime what string are you converting to datetime? In your query, you are converting your datetime to a varchar. And where are you getting this sort from?

CH Guravaiah 22-Mar-13 10:43am

but doesn't sort the starttime and finishtime in datagrid. How can solve this query.

D.Nikolov 22-Mar-13 11:13am

The DataGrid sorts the column correctly for it's data type which is string. Have you tried to set Format(DisplayFormat or whatever the property name is) on your column and return datetime instead of string.
What DataGrid are you using? WPF, Silverlight, Windows Forms, WebForms?

3 solutions

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 · 2013-03-22T06:12:00

Sorry, but your question has totally nonsense.

If your type of StartDate, EndDate fields is DATETIME, you don't need to convert it to datetime type. It's similarly like: how to convert string to string?

To format DGV columns with dates, see this: http://msdn.microsoft.com/en-us/library/f9x2790s.aspx[^]

BTW, you got your answer here: How to Sort the DateTime Column in DataGridview[^]

In the future, do not repost. If you have a question (to the author of solution/answer), please, use "Have a question or comment" widget.

phil.o · Answer 2 · 2013-03-22T06:42:00

There is something we see on many posts these days, people use to handle DateTime values with strings rather than with reserved type.

In your query, instead of getting two DateTime values from your database, you transform them into strings. Which leads you to serious problems when you want to sort them ; because you make a string sort, which is quite different from a DateTime sort (which makes sense, if you think about it).

So, do you a favor, just get a DateTime value from your database ; your DatagridView will know how to handle a DateTime sort.
And, for the string representation of your DatTime value, use a specific DateTime.Format() overload, so you will get a string representation of your DateTime value in the formatting culture you want.

What is important is : do not try to format your values in the query ; format them when you display them only.

I hope this helps.

richardw48 · Answer 3 · 2013-03-22T05:07:00

Solution 1

Try changing the 'Order By' clause to "Order By BENCHMARKNO, STARTDATE". This will sort your records first by benchmarkno and then by startdate.

Posted 22-Mar-13 5:07am

richardw48

How to convert String to Date in Sql server 2008

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0