Converting from VARCHAR to DATETIME

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Converting from VARCHAR to DATETIME

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-03 : 08:36:25

Dave writes "I have an SQL 2000 database, which has the majority of fields set to VARCHAR.

One particular column, contains dates inserted from an ASP form. I need to produce a report based on these dates. The dates are entered as 30/07/01 16:47:35

How do I convert VARCHAR data to DATETIME so I can

SELECT <fields> from <tables>
WHERE <fieldname> BETWEEN <todays date> AND <date a week ago>

Please get back to me quickly as I have a paying client going mad about this. The major problem is that I'm not allowed to alter the datatypes to DATETIME (which would cure my problem), so I have to leave the VARCHAR field with the date in as is !

My main SQL Query is:

SELECT dbo.List.Acc_No, dbo.List.Customer_Name,
dbo.List.Address1 + '
' + dbo.List.Address2 + '
' + dbo.List.Address3 + '
' + dbo.List.Address4 AS InvoiceAddress, dbo.List.Postcode,
dbo.List.email, dbo.login_details.vvaccount_number, dbo.login_details.email_address, LEFT(dbo.OrderDetails.product_date_ordered, 8)
AS Date_Ordered, dbo.OrderDetails.product_quantity, dbo.OrderDetails.product_description, dbo.OrderDetails.product_price,
dbo.OrderDetails.product_vehicle AS vehicles, dbo.OrderDetails.product_numbers AS Reg_nos, dbo.OrderDetails.OrderID,
dbo.customer_orders.order_ref_num, dbo.OrderDetails.product_code, dbo.OrderDetails.product_date_ordered
FROM dbo.OrderDetails INNER JOIN
dbo.customer_orders ON dbo.OrderDetails.OrderID = dbo.customer_orders.OrderID INNER JOIN
dbo.List INNER JOIN
dbo.login_details ON dbo.List.Acc_No = dbo.login_details.vvaccount_number ON
dbo.customer_orders.account_num = dbo.login_details.vvaccount_number AND dbo.customer_orders.account_num = dbo.List.Acc_No
WHERE (dbo.login_details.vvaccount_number <> N'A00113') AND (dbo.customer_orders.order_ref_num <> N'') AND (dbo.OrderDetails.product_date_ordered BETWEEN '25/11/01' AND '28/11/01')"

sica
Posting Yak Master

143 Posts

Posted - 2001-12-03 : 09:32:45

I'm not sure if I get it right,but tried that:

DECLARE @Field VARCHAR(17)

SET @Field = '30/07/01 16:47:35'

SELECT CONVERT(DATETIME,'20'+STUFF(STUFF(@Field,1,2,SUBSTRING(@Field,7,2)),7,2,SUBSTRING(@Field,1,2)))

You have to do a CASE if the string is beginning w/ 0 the add 20 else 19.

Is this what you what?

Sica

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-03 : 09:35:23

for that format it is convert(datetime,charfld,3)

see bol for other styles.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

sica
Posting Yak Master

143 Posts

Posted - 2001-12-03 : 10:04:42

Much easier :)-

Sica

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources