Time conversion in SQL (urgent) 2

[abs2003]

Hi all,
My SQL database table contains String data such as
January 2005
March 2006
April 2005
and so on..

I need to run a query to pull data from this table SORTED by this field as datetime. How can I do this?

Thank you

 

[ca8msm]

Does your table contain the real date as well or is that the only date you have?

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[abs2003]

ca8Msm,
No, the data is in string type and it's only date I have.

 

[SqueakinSweep]

If the string is the only date you have, as you make it sound like, then you have 2 options.

1) Add a new field to your SQL Table, and write a little code to generate a datetime from the string. Simple enough but time consuming.

2) Write a SQL Based Function to generate something like '2005-01' for January 2005.

Sweep
...if it works dont mess with it

 

[abs2003]

Good thinking Sweep.

Thank you.

 

[ca8msm]

I think you can probably do it much easier that that. Try something like:

SELECT     MyDate
FROM         MyTable
ORDER BY MONTH('1 ' + MyDate)

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[ca8msm]

Sorry, just realised you had different years in there as well so you would need to order by the year as well:

SELECT     MyDate
FROM         MyTable
ORDER BY YEAR(MyDate), MONTH('1 ' + MyDate)

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[abs2003]

Guys,

Thank you for all your help. I have figured it out with ca8msm help.

SELECT     DueDate, [date], STR(YEAR(DueDate)) + STR(MONTH(DueDate)) AS REALDATE
FROM         tblDetail
ORDER BY STR(YEAR(DueDate)) + STR(MONTH(DueDate))