Date format isn't working

Hello, all. We've got a table that holds the begin and end date for allowing
people into a voting app. Here's the DDL,
CREATE TABLE [tblVotingPeriod] (
[pk] [tinyint] IDENTITY (1, 1) NOT NULL ,
[beginVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_beginVote]
DEFAULT (getdate()),
[endVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_endVote]
DEFAULT (getdate()),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_dateAdded]
DEFAULT (getdate()),
CONSTRAINT [PK_tblVotingPeriod] PRIMARY KEY CLUSTERED
(
[pk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
We need it to store "3/15/2005 12:00:00 AM" as the beginVote and "3/18/2005
12:00:00 AM" as the endVote. However, each time we try entering the
"12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
won't display the time in the ASP page -- just the date. We've tried using
the CONVERT(smalldatetime, beginVote,109) to no avail. Any ideas? Thanks
much."dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 12:00:00 AM" as the endVote. However, each time we try entering the
> "12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
Sounds like a problem with your client code, not SQL Server. SQL Server
DATETIME and SMALLDATETIME datatypes must have a time component -- they
can't be ignored. Or are you getting an error of some sort?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
and tab out, it ignores it. Is there something special about 12:00:00 AM? Is
that the "default" for the date, so it doesn't even show it? What if you do
want it shown?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O7BswobGFHA.544@.TK2MSFTNGP12.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with your client code, not SQL Server. SQL
> Server
> DATETIME and SMALLDATETIME datatypes must have a time component -- they
> can't be ignored. Or are you getting an error of some sort?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
> date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
> and tab out, it ignores it. Is there something special about 12:00:00 AM?
Is
> that the "default" for the date, so it doesn't even show it? What if you
do
> want it shown?
EM does appear to truncate the date if the time is 12:00:00 AM. And
yes, that is the "default" time -- if you insert a row with '20050315' and
no time component, the time will automatically be set to 12:00:00 AM.
(Actually, 00:00:00). You should be able to format the date to see the time
component in ASP using VBScript's date formatting functions. It is being
returned by SQL Server, you're just not displaying it. Note also, Query
Analyzer does not trucate the date.
One side note, by the way: You should re-consider that date format, as
it's ambiguous (it can change with locale); for example, what does the
following represent: '01/02/2005' ? Depends on what country you're in.
The preferred format in SQL Server is YYYYMMDD.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
just not being shown. Do you know of a less ambigious date format? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
> just not being shown. Do you know of a less ambigious date format? Thanks.
YYYYMMDD HH:MM:SS - '20050315 00:00:00'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Which format do I use if I want the date to come out "Mar 21 2005"? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||Thanks, Adam. I got it working, thanks to your help! I had a mistake in my
code where I was doing the convert -- instead of converting it to a char or
varchar, I was changing it to a smalldatetime, which it already was! I'm
using the 100 format to convert it to this: Mar 15 2005 12:00AM
Thanks for your help :) Much appreciated.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e6D5c9bGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> YYYYMMDD HH:MM:SS - '20050315 00:00:00'
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||I suggest you check out my article on the subject:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message news:eTbYl9bGFHA.2356@.TK2MSFTNGP12.phx.g
bl...
> Which format do I use if I want the date to come out "Mar 21 2005"? Thanks
.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
>

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