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...
>
Showing posts with label holds. Show all posts
Showing posts with label holds. Show all posts
Thursday, March 8, 2012
Friday, February 17, 2012
date conversion
hi friends,
I want to convert datetime to date.
example
select dt from abc where dt between @.dt1 and @.dt2
dt holds the value '2004-08-02 12:12:28.000'
i want dt to eliminame time before checking.
thanks
vanithaVanitha
You can use
select dt from abc where dt >=@.dt1 and dt< dateadd(day,1,@.dt2) ( It gives an
ability to use an index on dt column)
Lookup CONVERT function in the BOL
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:A2648D8E-CCD2-45DF-91AB-5DC852203A2C@.microsoft.com...
> hi friends,
> I want to convert datetime to date.
> example
> select dt from abc where dt between @.dt1 and @.dt2
> dt holds the value '2004-08-02 12:12:28.000'
> i want dt to eliminame time before checking.
> thanks
> vanitha
>|||SELECT CONVERT(DATETIME,(CONVERT(VARCHAR(12), Getdate(),7)))
"Vanitha" wrote:
> hi friends,
> I want to convert datetime to date.
> example
> select dt from abc where dt between @.dt1 and @.dt2
> dt holds the value '2004-08-02 12:12:28.000'
> i want dt to eliminame time before checking.
> thanks
> vanitha
>|||> SELECT CONVERT(DATETIME,(CONVERT(VARCHAR(12), Getdate(),7)))
This will fail if you have certain regional settings, e.g. SET LANGUAGE
RUSSIAN
Much safer to use ISO standard date formats, like YYYYMMDD; in this specific
case, better to use a real datetime instead of relying on string formatting,
then you can use an index (which is ideal for this type of query).
DECLARE @.dt SMALLDATETIME
SET @.dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT ...
WHERE dt >= @.dt AND dt < @.dt+1
I want to convert datetime to date.
example
select dt from abc where dt between @.dt1 and @.dt2
dt holds the value '2004-08-02 12:12:28.000'
i want dt to eliminame time before checking.
thanks
vanithaVanitha
You can use
select dt from abc where dt >=@.dt1 and dt< dateadd(day,1,@.dt2) ( It gives an
ability to use an index on dt column)
Lookup CONVERT function in the BOL
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:A2648D8E-CCD2-45DF-91AB-5DC852203A2C@.microsoft.com...
> hi friends,
> I want to convert datetime to date.
> example
> select dt from abc where dt between @.dt1 and @.dt2
> dt holds the value '2004-08-02 12:12:28.000'
> i want dt to eliminame time before checking.
> thanks
> vanitha
>|||SELECT CONVERT(DATETIME,(CONVERT(VARCHAR(12), Getdate(),7)))
"Vanitha" wrote:
> hi friends,
> I want to convert datetime to date.
> example
> select dt from abc where dt between @.dt1 and @.dt2
> dt holds the value '2004-08-02 12:12:28.000'
> i want dt to eliminame time before checking.
> thanks
> vanitha
>|||> SELECT CONVERT(DATETIME,(CONVERT(VARCHAR(12), Getdate(),7)))
This will fail if you have certain regional settings, e.g. SET LANGUAGE
RUSSIAN
Much safer to use ISO standard date formats, like YYYYMMDD; in this specific
case, better to use a real datetime instead of relying on string formatting,
then you can use an index (which is ideal for this type of query).
DECLARE @.dt SMALLDATETIME
SET @.dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT ...
WHERE dt >= @.dt AND dt < @.dt+1
Tuesday, February 14, 2012
Date & Time Formatting
Hi Everyone,
I am failry new to SQL & am trying to write a view over a table that has a
filed called TIME. the field holds the date & time in the usual format i.e.
dd/mm/yyyy:hh:mm:ss
I want to be able to have the field formatted in the view just to show the
hour portion of the field as we can in excel but how? I know I can set the
field to DATE but how to HOUR?
Regards
TIA> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format
> i.e.
> dd/mm/yyyy:hh:mm:ss
No it doesn't. There is no "usual format" since the information is stored
as numeric information.
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set
> the
> field to DATE but how to HOUR?
Presentation of data is best left to the client application. Best you read
the following link (and the entire site when you have time).
http://www.karaszi.com/sqlserver/info_datetime.asp|||You can use the datepart function in the view. i.e. datepart(hh,getdate())
This example returns the hour for the supplied date.
"Scott Morris" wrote:
> > I am failry new to SQL & am trying to write a view over a table that has a
> > filed called TIME. the field holds the date & time in the usual format
> > i.e.
> > dd/mm/yyyy:hh:mm:ss
> No it doesn't. There is no "usual format" since the information is stored
> as numeric information.
> > I want to be able to have the field formatted in the view just to show the
> > hour portion of the field as we can in excel but how? I know I can set
> > the
> > field to DATE but how to HOUR?
> Presentation of data is best left to the client application. Best you read
> the following link (and the entire site when you have time).
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
>|||Hi Jonathan,
try this:
SELECT DATEPART(hour, fieldname) AS 'Hour'
from tablename
GO
Hope this helps,
Isobel
"Jonathan" wrote:
> Hi Everyone,
> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format i.e.
> dd/mm/yyyy:hh:mm:ss
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set the
> field to DATE but how to HOUR?
> Regards
> TIA
>
I am failry new to SQL & am trying to write a view over a table that has a
filed called TIME. the field holds the date & time in the usual format i.e.
dd/mm/yyyy:hh:mm:ss
I want to be able to have the field formatted in the view just to show the
hour portion of the field as we can in excel but how? I know I can set the
field to DATE but how to HOUR?
Regards
TIA> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format
> i.e.
> dd/mm/yyyy:hh:mm:ss
No it doesn't. There is no "usual format" since the information is stored
as numeric information.
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set
> the
> field to DATE but how to HOUR?
Presentation of data is best left to the client application. Best you read
the following link (and the entire site when you have time).
http://www.karaszi.com/sqlserver/info_datetime.asp|||You can use the datepart function in the view. i.e. datepart(hh,getdate())
This example returns the hour for the supplied date.
"Scott Morris" wrote:
> > I am failry new to SQL & am trying to write a view over a table that has a
> > filed called TIME. the field holds the date & time in the usual format
> > i.e.
> > dd/mm/yyyy:hh:mm:ss
> No it doesn't. There is no "usual format" since the information is stored
> as numeric information.
> > I want to be able to have the field formatted in the view just to show the
> > hour portion of the field as we can in excel but how? I know I can set
> > the
> > field to DATE but how to HOUR?
> Presentation of data is best left to the client application. Best you read
> the following link (and the entire site when you have time).
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
>|||Hi Jonathan,
try this:
SELECT DATEPART(hour, fieldname) AS 'Hour'
from tablename
GO
Hope this helps,
Isobel
"Jonathan" wrote:
> Hi Everyone,
> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format i.e.
> dd/mm/yyyy:hh:mm:ss
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set the
> field to DATE but how to HOUR?
> Regards
> TIA
>
Date & Time Formatting
Hi Everyone,
I am failry new to SQL & am trying to write a view over a table that has a
filed called TIME. the field holds the date & time in the usual format i.e.
dd/mm/yyyy:hh:mm:ss
I want to be able to have the field formatted in the view just to show the
hour portion of the field as we can in excel but how? I know I can set the
field to DATE but how to HOUR?
Regards
TIA> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format
> i.e.
> dd/mm/yyyy:hh:mm:ss
No it doesn't. There is no "usual format" since the information is stored
as numeric information.
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set
> the
> field to DATE but how to HOUR?
Presentation of data is best left to the client application. Best you read
the following link (and the entire site when you have time).
http://www.karaszi.com/sqlserver/info_datetime.asp|||You can use the datepart function in the view. i.e. datepart(hh,getdate())
This example returns the hour for the supplied date.
"Scott Morris" wrote:
> No it doesn't. There is no "usual format" since the information is stored
> as numeric information.
>
> Presentation of data is best left to the client application. Best you rea
d
> the following link (and the entire site when you have time).
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
>|||Hi Jonathan,
try this:
SELECT DATEPART(hour, fieldname) AS 'Hour'
from tablename
GO
Hope this helps,
Isobel
"Jonathan" wrote:
> Hi Everyone,
> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format i.
e.
> dd/mm/yyyy:hh:mm:ss
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set th
e
> field to DATE but how to HOUR?
> Regards
> TIA
>
I am failry new to SQL & am trying to write a view over a table that has a
filed called TIME. the field holds the date & time in the usual format i.e.
dd/mm/yyyy:hh:mm:ss
I want to be able to have the field formatted in the view just to show the
hour portion of the field as we can in excel but how? I know I can set the
field to DATE but how to HOUR?
Regards
TIA> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format
> i.e.
> dd/mm/yyyy:hh:mm:ss
No it doesn't. There is no "usual format" since the information is stored
as numeric information.
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set
> the
> field to DATE but how to HOUR?
Presentation of data is best left to the client application. Best you read
the following link (and the entire site when you have time).
http://www.karaszi.com/sqlserver/info_datetime.asp|||You can use the datepart function in the view. i.e. datepart(hh,getdate())
This example returns the hour for the supplied date.
"Scott Morris" wrote:
> No it doesn't. There is no "usual format" since the information is stored
> as numeric information.
>
> Presentation of data is best left to the client application. Best you rea
d
> the following link (and the entire site when you have time).
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
>|||Hi Jonathan,
try this:
SELECT DATEPART(hour, fieldname) AS 'Hour'
from tablename
GO
Hope this helps,
Isobel
"Jonathan" wrote:
> Hi Everyone,
> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format i.
e.
> dd/mm/yyyy:hh:mm:ss
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set th
e
> field to DATE but how to HOUR?
> Regards
> TIA
>
Subscribe to:
Posts (Atom)