Formatting a date to drop the 'time' off

[C Kirby]

I've got a stored procedure in SQL Server 2000 that takes a date/time
(9/9/2003 10:14 AM) and inserts it (along with a lot of other fields)
into an existing table. I need a way to trim or drop the time part of
this field off or to set it to 9/9/2003 00:00.000

I've tried a few of the date formats, but it doesn't seem to actually
change the value.

Ex.

INSERT INTO dbo.tblAPTransHeader (TransID, BatchID, WhseId, VendorId,
InvoiceDate )
SELECT PONum,'BKTRNF', 'SVR', BillID, DelvDate
FROM #BSPU3

where DelvDate is the datetime field that need to be formatted.

 

[Van T. Dinh]

Can find anything useful in BOL but this works:

SELECT Convert(datetime,
Convert(varchar, GetDate(), 101), 101)

i.e. use Convert() twice to get rid of the time component.

I found this by trial & error and not from book references
so it may not be the right / most efficient method.

HTH
Van T. Dinh
MVP (Access)

 

[C Kirby]

Thanks for the help Van, I'll give that a try!

Can find anything useful in BOL but this works:

SELECT Convert(datetime,
Convert(varchar, GetDate(), 101), 101)

i.e. use Convert() twice to get rid of the time component.

I found this by trial & error and not from book references
so it may not be the right / most efficient method.

HTH
Van T. Dinh
MVP (Access)