Converting Date to Datetime in SQL

[tmishue]

Hello Out There,

Can anyone help me in converting a date such as
Mon Jan 15 00:00:00 EST 2001 to mm/dd/yyy. I have a field name StartDate with a datatype of datetime. I don't care about the time.

I am using Active X Calendar Control.value to get my date when the user click on it. Then I am querying my SQL database where StartDate = CalendarControl.Value . I can't get the query to work because the CalendarControl.Value is in the wrong format- Mon Jan 15 00:00:00 EST 2001

Any comments or suggestions would be greatly appreciated.


Thanks Much!

tammym

 

[tlbroadbent]

I'm unsure of the client software in use. I would choose to convert the date in the client with various string functions such as left, right, mid, substring or whatever. However, you can do the same in T-SQL using similar functions.

The following code shows how to convert the date/time string you describe in a T-SQL procedure.

Create Procedure sp_MyProc @dt varchar(28) As

Select * From tbl
Where StartDate=convert(datetime,substring(@dt,5,6) + ' ' + right(@dt,4))

Execute the procedure

Exec sp_MyProc 'Mon Jan 15 00:00:00 EST 2001 '

Create a query string in you APP with code like the following.

SQL="Select * From tbl Where StartDate=convert(datetime,substring(" & CalendarControl.Value & ",5,6) + ' ' + right(" & CalendarControl.Value & "@dt,4))"

Hope this helps. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tmishue]

For Closure:
I got this answer from Rob (rjr9999) on one of the other forums. Thanks to all for taking the time to help me. It was greatly appreciated.

tmishue

The answer that worked for me:

rjr9999 (Programmer) Jul 3, 2001
I just tried this for fun, Hopefully it will help

I used the Microsoft calendar control 8.0 and did this...

Dim tdyDate As String
tdyDate = Calendar1.Month & "/" & Calendar1.Day & "/" & Calendar1.Year
Label1.Caption = tdyDate

my label had the format of mm/dd/yyyy....let me know if this helps

While you're waiting, i'll check with the other calender controls

Rob

 

[tlbroadbent]

Rob's answer is exactly in accordance with my recommendation to convert the string in your client application rather than SQL Server. I'm glad you found the answer. It is much cleaner than a SQL solution. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tmishue]

Terry,

Thanks for the tip. I am new to SQL and MS InterDev(as you can obviously tell). To tell the truth I wasn't real sure which forum to ask. Thanks for your direction.

Again I appreciate your taking time to answer me.(|>

tmishue