Help! Date formatting nightmare!

I converted an Access Database to SQL Express. The dates were converted to datetime

I'm using VWD 2005

Here is the source of my date and the query.

sqlDate = (DateTime.Now.AddDays(-7))

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 101)) ORDER BY Service_Orders.SDate_Entered DESC"

This retrurns 0 records.

sqlDate =11/28/2005 12:23:27 AM from the function above.

The querywill return records with :

sqlDate ="2005-11-01 21:56:20"

I tried changing the CONVERT(DATETIME, '" + sqlDate + "',1XX from 100 to 120 with no luck

I know this must be an easy fix, but it is beyond me.

I need to know how to

1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20"

or

2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly

Thanks for any help in advance!

Bill

I always convert them after I recieved the data from the database so the way how you do it depends on where you handle them afterwards. Datagrid has it's own dataformatstring property and incode you can do it like this for an example:
lblFormat.Text = String.Format("{0:dd.MM.yyyy}", myData("myField"))
Find outmore
|||

OK here is the problem:

When I run my query, the query return 0 rows.

I narrowed the problem down to the fact that it doesn't like my date.

I construct the date via the function sqlDate = (DateTime.Now.AddDays(-7))

When I pass that date into my query 0 rows are returned. If I hard code the date with

sqlDate ="2005-11-01 21:56:20" I get all the rows for that date.

Here is my query

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 102)) ORDER BY Service_Orders.SDate_Entered DESC"

The field SDate_Entered is a datetime field.

Who would make a product where a simple query is such a problem??