1

I have one column in my database that I need to convert from datetime to the date data type using a SQL Server function. I can't figure out how to get the correct syntax. Could I get some help with the syntax so IntelliSense will stop yelling at me and I can get the query to run?

CREATE FUNCTION fChangeDateFormat (@date01 date)
RETURNS DATE
AS

RETURN(
SELECT
    Convert(DateTime, OrderDate, 101)
FROM
    Orders
WHERE 
    OrderDate = @date01
)
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
ShreddedSaber
  • 125
  • 1
  • 11
  • `CONVERT` is already a function, why do you want to wrap it in another one? Are you looking to permanently alter the datatype of your table column or do the conversion on-the-fly for reporting purposes? – Cᴏʀʏ Aug 17 '17 at 02:25
  • I need to alter the datatype of the column permanently. What do you mean by on-the-fly? – ShreddedSaber Aug 17 '17 at 02:30
  • You don't need to use a function to alter the column, I would simply add a new column, then run an UPDATE query to populate it, then drop the old column. Just seems like you're over-complicating the task. – Cᴏʀʏ Aug 17 '17 at 15:36

1 Answers1

1

You can use convert as,

select CONVERT(date,@date01,101) as dd from Orders

If you are using SQl server 2012 + use FORMAT, 'd' is to get the short date format.

SELECT FORMAT(@date01,'d','en-US') as dd from Orders
Dhanuka777
  • 8,331
  • 7
  • 70
  • 126