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...
>