Updating a date field without the "time" 1

[DebbieDavis]

Greetings,

I have a date field with the time, but I would like it not to have the time. Is there a way to update that field with the date only? i.e. instead of 6/4/2003 4:55:00 PM I only want to show 6/4/2003... I have created another field with the datetime format. The original field with the time has the smalldate format but has a default of getdate(). Many thanks for your thoughts.

 

[ecobb]

SQL Server always stores the date AND time, if you don't specify a time, then it defaults to 00:00:00. If you don't specify a date then it will default to 01/01/1900.
That's just the nature of the beast...

 

[SQLBill]

You can script your front-end application to return only the date portion.

-SQLBill

 

[DebbieDavis]

Thanks, SQLBill. I'm using the query in an active server page. I tried a zillion different ways with CONVERT but failed.

select * from orders where invoicedate between '" & startdate & "' and '" & enddate & "'"

where invoicedate is the field I need to remove the time from.

I tried (CONVERT(VARCHAR(25), (invoicedate), 1)) and thought it would work but did not. SO I created an update query with (CONVERT(VARCHAR(25), (invoicedate), 1)) that will update the invoicedate field with itself everytime the orders report runs. It's clunky but it seems to work ok. If you can think of something else, let me know. Thanks a million!
dd

 

[iArchitect]

To retrieve you results in the mm/dd/yyyy format, try the following two things:

1.
Create a function in SQL Server using the following code:
--start of code
CREATE FUNCTION dbo.DateOnly(@Date datetime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12), @Date, 101)
END
--end of code

2. In you select statement, call the dbo.DateOnly function passing in the date field in question as a parameter.
Ex. (Using the Northwind Database)

select dbo.DateOnly(HireDate)
as HireDate
from employees

Notice that the 2nd line in the sql statement has an alias of "HireDate". This is needed so that you can access the field by its name. You can of course use any alias name you choose.

Good Luck!
James Flynn