CODEWITHSUNDEEP
sqlsql-servertype-conversion
jackhammer013
jackhammer013

Reputation: 2297

SQL Convert NVARCHAR to Datetime on Query

I have an SQL Function that retrieves data but I have a problem on the database, the one who handled it before made the field a NVARCHAR instead of DATETIME, so now I can't sort out the date or select the data between ranges.

Supposed I have a query:

Select order_confirmation.oc_number as oc,
order_confirmation.count as cnt,
order_confirmation.status as stat,
order_confirmation.po_number as pon,
order_summary.date_delivered as dd,
order_summary.delivery_quantity as dq,
order_summary.is_invoiced as iin,
order_summary.filename as fn,
order_summary.invoice_number as inum,
order_summary.oc_idfk as ocidfk,
order_summary.date_invoiced as di
FROM
order_confirmation,order_summary
where order_confirmation.id = order_summary.oc_idfk
order by order_summary.date_delivered DESC

I want to convert order_summary.date_delivered to a DATETIME format so that the ORDER BY statement would work properly. Please take note that I don't want to change the datatype permanently on the database structure, only in the query.I tried searching and trying other's solution but won't work plus I'm not yet familiar with SQL Server.

Thanks

Helpers:

enter image description here

enter image description here

Upvotes: 1

Views: 1393

Answers (2)

jradich1234
jradich1234

Reputation: 1425

UPDATED Based on Iconic's good point and to hopefully correct your overflow issue Use format 105 with your CONVERT

Select order_confirmation.oc_number as oc,
order_confirmation.count as cnt,
order_confirmation.status as stat,
order_confirmation.po_number as pon,
order_summary.date_delivered as dd,
order_summary.delivery_quantity as dq,
order_summary.is_invoiced as iin,
order_summary.filename as fn,
order_summary.invoice_number as inum,
order_summary.oc_idfk as ocidfk,
order_summary.date_invoiced as di
FROM
order_confirmation,order_summary
where order_confirmation.id = order_summary.oc_idfk
order by CONVERT(datetime, order_summary.date_delivered, 105) DESC

Upvotes: -1

apomene
apomene

Reputation: 14389

on your order by clause use:

order by convert(datetime,order_summary.date_delivered, X)

where X=100,101,102... depending on the format you wish to have

Here you can see more details for convert function

Upvotes: 3

Related Questions