Date field

Ho do you set the date field in an SQL SERVER table.
I only want to show just the date without time in my col.

Isnt there a DATE type for SQL table.
I have a big problem with dates as I have imported all
my datas from Access MDB.
Tnx
Hi there,

There isn't a data type in SQL Server that stores only the date. The only date/time data types in SQL Server are datetime and smalldatetime and these will clearly store a date in addition to a time.

A little hack you can use is to add another column to your table that stores only the date. The data type of this column can be one of the character data types (e.g. CHAR, VARCHAR). This isn't so bad because the character values can still be considered as dates in terms of comparison as long as they are in a recognised format (like mm/dd/yyyy)....

If you are comparing them to something like a datetime or smalldatetime value then the character value gets converted automatically to a date time with the time component (I think) being midnight of that day.

If you want to compare two character representations of a date, you can use something like the CAST function to change them both to datetime or smalldatetime values before doing the comparison. Once again, the conversion will have a time component of midnight of that day. This works in your favour because the time components of the two character values will be the same so any difference will be purely down to the date.

Anyway, onto the meat of this post....Below is a function that will take a datetime value and ouput a character representation of the date:

CREATE FUNCTION dbo.convToDateOnly
(
@.dateTimeToConvert DATETIME
)
RETURNS CHAR(10) -- Returns in dd/mm/yyyy so always 10 characters
AS
BEGIN
DECLARE @.convertedDate CHAR(10)

-- These are the individual date components in a numeric format
DECLARE @.year INT
DECLARE @.month INT
DECLARE @.day INT

-- Together these will form the dates that we want
DECLARE @.yearAsChar CHAR(4)
DECLARE @.monthAsChar CHAR(2)
DECLARE @.dayAsChar CHAR (2)

SET @.day = DATEPART(dd, @.dateTimeToConvert)
SET @.month = DATEPART(mm, @.dateTimeToConvert)
SET @.year = DATEPART(yyyy, @.dateTimeToConvert)

-- If the values for days and months is less than 10, when they are converted to character data
-- they will only have a length of 1...In that case, we will pad with a leading 0
IF @.day < 10 SET @.dayAsChar = '0' + CAST(@.day AS CHAR(1)) ELSE SET @.dayAsChar = CAST(@.day AS CHAR(2))
IF @.month < 10 SET @.monthAsChar = '0' + CAST(@.month AS CHAR(1)) ELSE SET @.monthAsChar = CAST(@.month AS CHAR(2))

SET @.yearAsChar = CAST(@.year AS CHAR(4))

-- Change the order of the components to get different formats like mm/dd/yyyy. As an example, I have
-- placed my stuff in dd/mm/yyyy format
SET @.convertedDate = @.dayAsChar + '/' + @.monthAsChar + '/' + @.yearAsChar

RETURN @.convertedDate
END
GO

It's not particularly subtle or clever, but it seems to do OK. Depending on where you are, you will probably want a date representation in mm/dd/yyyy so you can switch the order in which I form the value of the @.convertedDate variable to get what you want.

After defining this function, you can modify your table to have an extra column for the date as a character value and populate it. Below is a full example from table creation to adding the column to populating the new column:

NB. You probably will not go through all these steps...Most likely, you will start at adding a new column. For the sake of completeness of the example, though, I have gone through the whole thing.

-- This bit creates the table
CREATE TABLE dbo.DateTest
(
[Key]INT IDENTITY(1, 1) NOT NULL,
[DT] DATETIME
)
GO

-- This bit populates it with dates
DECLARE @.counter INT

SET @.counter = 1
WHILE @.counter < 20
BEGIN
INSERT INTO dbo.DateTest VALUES(GETDATE())
SET @.counter = @.counter + 1
END
GO

-- This shows what we have so far
SELECT *
FROM dbo.DateTest
GO

-- This adds a column to store dates only
ALTER TABLE dbo.DateTest
ADD dateAsThing char(10) NULL
GO

-- This puts values in the column we just created
UPDATE dbo.DateTest SET [dateAsThing] = dbo.convToDateOnly([DT])
GO

-- This shows the results
SELECT *
FROM dbo.DateTest
GO

Note that you could also add a default constraint to the new column you add so that whenever you enter a new row in the table the column containing the character representation of the date is automatically populated....Could get complicated though, but the choice is yours.

Hope that helps a bit, but sorry if it doesn't