Listing 3: Sets and converts a Datetime Value

Alter Procedure converted_times
AS
--This line doesn’t remove anything when you run it from
--an Access stored procedure template
SET NOCOUNT ON

--Datetime declarations
DECLARE @mydatetime DATETIME
DECLARE @mydatetimeDefault DATETIME
DECLARE @mydatetimess DATETIME
DECLARE @mydatetimeDefaultms DATETIME

--Store datetime value
SET @mydatetime=getdate()

--Convert datetime value for formatting with
--default, minute, second, and millisecond representations
SET @mydatetimeDEFAULT = CONVERT(varchar,@mydatetime,100)
SET @mydatetimess = CONVERT(varchar,@mydatetime,108)
SET @mydatetimeDefaultms = CONVERT(varchar,@mydatetime,109)

--Milliseconds don’t display from Access stored procedure template,
--but they do display from Query Analyzer
SELECT @mydatetime AS 'Raw DateTime', @mydatetimeDefault AS 'Default',
	@mydatetimess AS 'Seconds', @mydatetimeDefaultms AS 'Defaultms'