question

maaz88 avatar image
maaz88 asked

How to separate a particular year data from a column of multiple years

I have a database table with a column of date which is varchar datatype. The table has about 4000 entries for 7 years, I need to sort it datewise but the convert command does not work for the date column to convert it from mm/dd/yyyy to yyyy/mm/dd. Is there any solution to this or otherwise tell me how to separate a particular year's dates from this column to a separate table. Thanks.
sorting
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
please post some rows of the data as you have it and an example of how you want to have your results
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Remember, if an answer to your question is helpful, vote it up by clicking on the thumbs up symbol next to it. If the answer solves your problem, be sure to click the check box next it to identify it as the solution. Do this for all your questions.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
You should be able to use style 101 to convert from mm/dd/yyyy, as long as the varchar values are genuine dates declare @YourTable table ([date] varchar(50)) insert into @YourTable select '02/07/2013' insert into @YourTable select '12/07/2013' insert into @YourTable select '10/07/2013' insert into @YourTable select '02/01/2013' select [date], convert(datetime, [date], 101) from @YourTable order by convert(datetime, [date], 101) gives date ------------ ----------------------- 02/01/2013 2013-02-01 00:00:00.000 02/07/2013 2013-02-07 00:00:00.000 10/07/2013 2013-10-07 00:00:00.000 12/07/2013 2013-12-07 00:00:00.000 Don't worry about converting it to yyyy/mm/dd - just convert the value to a datetime, and then let SQL do the sorting
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.