Save time in hh:mm:ss AM/PM format

Save time in hh:mm:ss AM/PM format

Post by Aami » Sun, 02 Jun 2002 05:14:12



Hi gurus,
Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
AM/PM format only without a prefix date mm/dd/yyyy.
I am trying to save time value only in a stored procedure which is
originally a string value and I am casting it to smalldatetime format
before updating it to the table. But SQL by default puts '1/1/1900' in
front of my time. Does it have to do that. I want plain simple time in
my field, what is the way of doing it

thanks

aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by BP Margoli » Sun, 02 Jun 2002 05:43:52


Aamir,

Quote:> Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> AM/PM format only without a prefix date mm/dd/yyyy.

No. Both the smalldatetime and datetime data types store **both** a data and
a time.

You can retrieve the time only portion via:

select convert(char(8), getdate( ), 108)
select right(convert(varchar, getdate( ), 109), 14)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hi gurus,
> Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> AM/PM format only without a prefix date mm/dd/yyyy.
> I am trying to save time value only in a stored procedure which is
> originally a string value and I am casting it to smalldatetime format
> before updating it to the table. But SQL by default puts '1/1/1900' in
> front of my time. Does it have to do that. I want plain simple time in
> my field, what is the way of doing it

> thanks

> aamir


 
 
 

Save time in hh:mm:ss AM/PM format

Post by David Joh » Thu, 06 Jun 2002 02:26:32


This answer appears throughout Usenet, that datetime and smalldatetime
store both a date and time, no matter what.  Technically, this is
correct.  However, this is a way to achieve what the original poster
is looking for.  Believe it or not, if you put the date "12/30/1899"
in front of your time, SQL will display *only* the time for the value
of the field.  It sounds bizarre, but try it!

For an INSERT statement, try the following:

"UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM' WHERE....."

Examine the contents of the table.  You will find that the value
"3:00:00 PM" is all that appears within the field.  I believe this is
what people who are asking this question are looking for.

As I mentioned, technically, the date is still there.  The problem is
that when only a time is displayed, 12/30/1899 is the assumed date.
This means that given two datetime values:

1.  "6/4/2002 1:00:00 PM"
2.  "4:00:00 PM"

SQL will actually evaluate value #2 as *less than* value #1, because a
date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do an
ORDER BY on your time column, you must have the same date in front of
the time in each row, or no date at all, but not a mixture, or your
sort will be quite wrong.

Hope this helps.  It sure drove me crazy for a while!

David Johns


> Aamir,

> > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > AM/PM format only without a prefix date mm/dd/yyyy.

> No. Both the smalldatetime and datetime data types store **both** a data and
> a time.

> You can retrieve the time only portion via:

> select convert(char(8), getdate( ), 108)
> select right(convert(varchar, getdate( ), 109), 14)

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Hi gurus,
> > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > AM/PM format only without a prefix date mm/dd/yyyy.
> > I am trying to save time value only in a stored procedure which is
> > originally a string value and I am casting it to smalldatetime format
> > before updating it to the table. But SQL by default puts '1/1/1900' in
> > front of my time. Does it have to do that. I want plain simple time in
> > my field, what is the way of doing it

> > thanks

> > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by Luc » Thu, 06 Jun 2002 04:04:56


"SQL" will display.... You mean the application you're using. Query Analyzer
does display the date as it should. So I guess that is a bug in your app.
client. Which SQL Server version are you using and which app?

create table Tablename([DateTime] datetime)
insert Tablename values(getdate())
go
UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM'
go
select * from TableName

Of course datetime datatypes are fixed length (that goes without saying)....
If you need to save space you could use a user-defined datatype.

Luc


> This answer appears throughout Usenet, that datetime and smalldatetime
> store both a date and time, no matter what.  Technically, this is
> correct.  However, this is a way to achieve what the original poster
> is looking for.  Believe it or not, if you put the date "12/30/1899"
> in front of your time, SQL will display *only* the time for the value
> of the field.  It sounds bizarre, but try it!

> For an INSERT statement, try the following:

> "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM' WHERE....."

> Examine the contents of the table.  You will find that the value
> "3:00:00 PM" is all that appears within the field.  I believe this is
> what people who are asking this question are looking for.

> As I mentioned, technically, the date is still there.  The problem is
> that when only a time is displayed, 12/30/1899 is the assumed date.
> This means that given two datetime values:

> 1.  "6/4/2002 1:00:00 PM"
> 2.  "4:00:00 PM"

> SQL will actually evaluate value #2 as *less than* value #1, because a
> date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do an
> ORDER BY on your time column, you must have the same date in front of
> the time in each row, or no date at all, but not a mixture, or your
> sort will be quite wrong.

> Hope this helps.  It sure drove me crazy for a while!

> David Johns




- Show quoted text -

> > Aamir,

> > > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > > AM/PM format only without a prefix date mm/dd/yyyy.

> > No. Both the smalldatetime and datetime data types store **both** a data
and
> > a time.

> > You can retrieve the time only portion via:

> > select convert(char(8), getdate( ), 108)
> > select right(convert(varchar, getdate( ), 109), 14)

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Hi gurus,
> > > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > I am trying to save time value only in a stored procedure which is
> > > originally a string value and I am casting it to smalldatetime format
> > > before updating it to the table. But SQL by default puts '1/1/1900' in
> > > front of my time. Does it have to do that. I want plain simple time in
> > > my field, what is the way of doing it

> > > thanks

> > > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by BP Margoli » Thu, 06 Jun 2002 05:14:06


David,

As Luc has posted, SQL Server will always return both the date and the time.
The client application may choose to suppress the date or the time portion
if it is set to certain values. Enterprise Manager, for example, normally
suppresses the time portion if the time is set to midnight, but that is a
function of EM, and not of SQL Server or of ADO.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> This answer appears throughout Usenet, that datetime and smalldatetime
> store both a date and time, no matter what.  Technically, this is
> correct.  However, this is a way to achieve what the original poster
> is looking for.  Believe it or not, if you put the date "12/30/1899"
> in front of your time, SQL will display *only* the time for the value
> of the field.  It sounds bizarre, but try it!

> For an INSERT statement, try the following:

> "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM' WHERE....."

> Examine the contents of the table.  You will find that the value
> "3:00:00 PM" is all that appears within the field.  I believe this is
> what people who are asking this question are looking for.

> As I mentioned, technically, the date is still there.  The problem is
> that when only a time is displayed, 12/30/1899 is the assumed date.
> This means that given two datetime values:

> 1.  "6/4/2002 1:00:00 PM"
> 2.  "4:00:00 PM"

> SQL will actually evaluate value #2 as *less than* value #1, because a
> date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do an
> ORDER BY on your time column, you must have the same date in front of
> the time in each row, or no date at all, but not a mixture, or your
> sort will be quite wrong.

> Hope this helps.  It sure drove me crazy for a while!

> David Johns




- Show quoted text -

> > Aamir,

> > > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > > AM/PM format only without a prefix date mm/dd/yyyy.

> > No. Both the smalldatetime and datetime data types store **both** a data
and
> > a time.

> > You can retrieve the time only portion via:

> > select convert(char(8), getdate( ), 108)
> > select right(convert(varchar, getdate( ), 109), 14)

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Hi gurus,
> > > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > I am trying to save time value only in a stored procedure which is
> > > originally a string value and I am casting it to smalldatetime format
> > > before updating it to the table. But SQL by default puts '1/1/1900' in
> > > front of my time. Does it have to do that. I want plain simple time in
> > > my field, what is the way of doing it

> > > thanks

> > > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by David Joh » Thu, 06 Jun 2002 13:46:14


Yes, yes, I know this.  I knew my post would be misconstrued.
Unfortunately, while I work with SQL all day, every day, I am not a
properly trained SQL Admin, and thus I don't know all the proper
terminology and whatnot.  I admit I haven't examined the results of my
experiment in query analyzer, only in Enterprise Manager, or the data
view in Visual Interdev, for example.  Also, I know now that having a
column that *attempts* to only store a time of day is a bad design.
There's no good reason not to use the datetime type as it was
intended, that is, to store both a date and a time in one place.

Unfortunately, my issue is a rather large, pre-existing web
application that makes extensive use of this bad design of a sepeate
"Time" field.  In the past, this application's database received its
inserts/updates using ADO 2.x recordsets, rather than direct SQL
statements.  ADO managed to store a time of day such that it
*appeared* that SQL was only storing the time itself.  My guess is
that the Update method of the recordset object sets the date to
12/30/1899 for any non-null datetime value which does not specify the
date component.  I know now that ADO was confusing me all along, but
it doesn't help my existing code which I am now attempting to migrate
to ASP.NET, and update using ADO Command objects, rather than
Recordsets.

Okay, this is getting off-topic, my apologies.  Don't mean to start a
debate, I think we're all saying similar things in different ways.
Thanks for the replies.  Feel free to correct me on any of this, I'm
always eager to learn more about the correct way to deal with SQL.

David Johns


> David,

> As Luc has posted, SQL Server will always return both the date and the time.
> The client application may choose to suppress the date or the time portion
> if it is set to certain values. Enterprise Manager, for example, normally
> suppresses the time portion if the time is set to midnight, but that is a
> function of EM, and not of SQL Server or of ADO.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > This answer appears throughout Usenet, that datetime and smalldatetime
> > store both a date and time, no matter what.  Technically, this is
> > correct.  However, this is a way to achieve what the original poster
> > is looking for.  Believe it or not, if you put the date "12/30/1899"
> > in front of your time, SQL will display *only* the time for the value
> > of the field.  It sounds bizarre, but try it!

> > For an INSERT statement, try the following:

> > "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM' WHERE....."

> > Examine the contents of the table.  You will find that the value
> > "3:00:00 PM" is all that appears within the field.  I believe this is
> > what people who are asking this question are looking for.

> > As I mentioned, technically, the date is still there.  The problem is
> > that when only a time is displayed, 12/30/1899 is the assumed date.
> > This means that given two datetime values:

> > 1.  "6/4/2002 1:00:00 PM"
> > 2.  "4:00:00 PM"

> > SQL will actually evaluate value #2 as *less than* value #1, because a
> > date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do an
> > ORDER BY on your time column, you must have the same date in front of
> > the time in each row, or no date at all, but not a mixture, or your
> > sort will be quite wrong.

> > Hope this helps.  It sure drove me crazy for a while!

> > David Johns



> > > Aamir,

> > > > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > > > AM/PM format only without a prefix date mm/dd/yyyy.

> > > No. Both the smalldatetime and datetime data types store **both** a data
>  and
> > > a time.

> > > You can retrieve the time only portion via:

> > > select convert(char(8), getdate( ), 108)
> > > select right(convert(varchar, getdate( ), 109), 14)

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > Hi gurus,
> > > > Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
> > > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > > I am trying to save time value only in a stored procedure which is
> > > > originally a string value and I am casting it to smalldatetime format
> > > > before updating it to the table. But SQL by default puts '1/1/1900' in
> > > > front of my time. Does it have to do that. I want plain simple time in
> > > > my field, what is the way of doing it

> > > > thanks

> > > > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by Luc » Fri, 07 Jun 2002 01:07:44


Interestingly I believe that this is a bug in ADO. For a DATETIME 1899 it's
not the first recognized date so if your application would need these dates
you have a problem (I believe 17xx is the first year accepted).


...

> Unfortunately, my issue is a rather large, pre-existing web
> application that makes extensive use of this bad design of a sepeate
> "Time" field.  In the past, this application's database received its
> inserts/updates using ADO 2.x recordsets, rather than direct SQL
> statements.  ADO managed to store a time of day such that it
> *appeared* that SQL was only storing the time itself.  My guess is
> that the Update method of the recordset object sets the date to
> 12/30/1899 for any non-null datetime value which does not specify the
> date component.  I know now that ADO was confusing me all along, but
> it doesn't help my existing code which I am now attempting to migrate
> to ASP.NET, and update using ADO Command objects, rather than
> Recordsets.

> Okay, this is getting off-topic, my apologies.  Don't mean to start a
> debate, I think we're all saying similar things in different ways.
> Thanks for the replies.  Feel free to correct me on any of this, I'm
> always eager to learn more about the correct way to deal with SQL.

> David Johns




- Show quoted text -

> > David,

> > As Luc has posted, SQL Server will always return both the date and the
time.
> > The client application may choose to suppress the date or the time
portion
> > if it is set to certain values. Enterprise Manager, for example,
normally
> > suppresses the time portion if the time is set to midnight, but that is
a
> > function of EM, and not of SQL Server or of ADO.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > This answer appears throughout Usenet, that datetime and smalldatetime
> > > store both a date and time, no matter what.  Technically, this is
> > > correct.  However, this is a way to achieve what the original poster
> > > is looking for.  Believe it or not, if you put the date "12/30/1899"
> > > in front of your time, SQL will display *only* the time for the value
> > > of the field.  It sounds bizarre, but try it!

> > > For an INSERT statement, try the following:

> > > "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM' WHERE....."

> > > Examine the contents of the table.  You will find that the value
> > > "3:00:00 PM" is all that appears within the field.  I believe this is
> > > what people who are asking this question are looking for.

> > > As I mentioned, technically, the date is still there.  The problem is
> > > that when only a time is displayed, 12/30/1899 is the assumed date.
> > > This means that given two datetime values:

> > > 1.  "6/4/2002 1:00:00 PM"
> > > 2.  "4:00:00 PM"

> > > SQL will actually evaluate value #2 as *less than* value #1, because a
> > > date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do an
> > > ORDER BY on your time column, you must have the same date in front of
> > > the time in each row, or no date at all, but not a mixture, or your
> > > sort will be quite wrong.

> > > Hope this helps.  It sure drove me crazy for a while!

> > > David Johns



> > > > Aamir,

> > > > > Is it possibleto save data in smalldatetime/datetime field in
HH:MM:SS
> > > > > AM/PM format only without a prefix date mm/dd/yyyy.

> > > > No. Both the smalldatetime and datetime data types store **both** a
data
> >  and
> > > > a time.

> > > > You can retrieve the time only portion via:

> > > > select convert(char(8), getdate( ), 108)
> > > > select right(convert(varchar, getdate( ), 109), 14)

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> >  which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > Hi gurus,
> > > > > Is it possibleto save data in smalldatetime/datetime field in
HH:MM:SS
> > > > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > > > I am trying to save time value only in a stored procedure which is
> > > > > originally a string value and I am casting it to smalldatetime
format
> > > > > before updating it to the table. But SQL by default puts
'1/1/1900' in
> > > > > front of my time. Does it have to do that. I want plain simple
time in
> > > > > my field, what is the way of doing it

> > > > > thanks

> > > > > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by BP Margoli » Fri, 07 Jun 2002 01:21:44


Luc,

While it is true that the earliest date that SQL Server will accept in a
datetime data type is Jan 1, 1753, SQL Server uses a base date of Jan 1
1900. Values after midnight Jan 1 1900 are (internally) positive values,
while values before midnight Jan 1 1900 are (internally) negative values.

However a number of other Microsoft products use different base dates ...
some with a year of 1899.

I wouldn't classify this behavior as a bug ... in ADO or elsewhere. It was
probably by design by the Microsoft developers, but admittedly the behavior
is not one that I necessarily agree with   :-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Interestingly I believe that this is a bug in ADO. For a DATETIME 1899
it's
> not the first recognized date so if your application would need these
dates
> you have a problem (I believe 17xx is the first year accepted).



> ...
> > Unfortunately, my issue is a rather large, pre-existing web
> > application that makes extensive use of this bad design of a sepeate
> > "Time" field.  In the past, this application's database received its
> > inserts/updates using ADO 2.x recordsets, rather than direct SQL
> > statements.  ADO managed to store a time of day such that it
> > *appeared* that SQL was only storing the time itself.  My guess is
> > that the Update method of the recordset object sets the date to
> > 12/30/1899 for any non-null datetime value which does not specify the
> > date component.  I know now that ADO was confusing me all along, but
> > it doesn't help my existing code which I am now attempting to migrate
> > to ASP.NET, and update using ADO Command objects, rather than
> > Recordsets.

> > Okay, this is getting off-topic, my apologies.  Don't mean to start a
> > debate, I think we're all saying similar things in different ways.
> > Thanks for the replies.  Feel free to correct me on any of this, I'm
> > always eager to learn more about the correct way to deal with SQL.

> > David Johns



> > > David,

> > > As Luc has posted, SQL Server will always return both the date and the
> time.
> > > The client application may choose to suppress the date or the time
> portion
> > > if it is set to certain values. Enterprise Manager, for example,
> normally
> > > suppresses the time portion if the time is set to midnight, but that
is
> a
> > > function of EM, and not of SQL Server or of ADO.

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > This answer appears throughout Usenet, that datetime and
smalldatetime
> > > > store both a date and time, no matter what.  Technically, this is
> > > > correct.  However, this is a way to achieve what the original poster
> > > > is looking for.  Believe it or not, if you put the date "12/30/1899"
> > > > in front of your time, SQL will display *only* the time for the
value
> > > > of the field.  It sounds bizarre, but try it!

> > > > For an INSERT statement, try the following:

> > > > "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM' WHERE....."

> > > > Examine the contents of the table.  You will find that the value
> > > > "3:00:00 PM" is all that appears within the field.  I believe this
is
> > > > what people who are asking this question are looking for.

> > > > As I mentioned, technically, the date is still there.  The problem
is
> > > > that when only a time is displayed, 12/30/1899 is the assumed date.
> > > > This means that given two datetime values:

> > > > 1.  "6/4/2002 1:00:00 PM"
> > > > 2.  "4:00:00 PM"

> > > > SQL will actually evaluate value #2 as *less than* value #1, because
a
> > > > date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do
an
> > > > ORDER BY on your time column, you must have the same date in front
of
> > > > the time in each row, or no date at all, but not a mixture, or your
> > > > sort will be quite wrong.

> > > > Hope this helps.  It sure drove me crazy for a while!

> > > > David Johns



> > > > > Aamir,

> > > > > > Is it possibleto save data in smalldatetime/datetime field in
> HH:MM:SS
> > > > > > AM/PM format only without a prefix date mm/dd/yyyy.

> > > > > No. Both the smalldatetime and datetime data types store **both**
a
> data
> > >  and
> > > > > a time.

> > > > > You can retrieve the time only portion via:

> > > > > select convert(char(8), getdate( ), 108)
> > > > > select right(convert(varchar, getdate( ), 109), 14)

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
etc.)
> > >  which
> > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > Hi gurus,
> > > > > > Is it possibleto save data in smalldatetime/datetime field in
> HH:MM:SS
> > > > > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > > > > I am trying to save time value only in a stored procedure which
is
> > > > > > originally a string value and I am casting it to smalldatetime
> format
> > > > > > before updating it to the table. But SQL by default puts
> '1/1/1900' in
> > > > > > front of my time. Does it have to do that. I want plain simple
> time in
> > > > > > my field, what is the way of doing it

> > > > > > thanks

> > > > > > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by Luc » Fri, 07 Jun 2002 03:38:50


I checked and you're indeed right! For compatibility reasons a base date of
1/1/1900 is used. Of course it really is a bug if an application connecting
to *SQL Server* displays no date since that's an incorrect client
interpretation of the SQL Server datatype. But then again some people may
rely on that behavior obviously :-(.

As far as I can tell QA and OSQL work fine. VB/ADO grid control indeed
truncates the time if it's midnight and truncates the date if it's
12/30/1899 (but not if it's 12/31/1899 which doesn't make sense).

Luc


> Luc,

> While it is true that the earliest date that SQL Server will accept in a
> datetime data type is Jan 1, 1753, SQL Server uses a base date of Jan 1
> 1900. Values after midnight Jan 1 1900 are (internally) positive values,
> while values before midnight Jan 1 1900 are (internally) negative values.

> However a number of other Microsoft products use different base dates ...
> some with a year of 1899.

> I wouldn't classify this behavior as a bug ... in ADO or elsewhere. It was
> probably by design by the Microsoft developers, but admittedly the
behavior
> is not one that I necessarily agree with   :-)

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Interestingly I believe that this is a bug in ADO. For a DATETIME 1899
> it's
> > not the first recognized date so if your application would need these
> dates
> > you have a problem (I believe 17xx is the first year accepted).



> > ...
> > > Unfortunately, my issue is a rather large, pre-existing web
> > > application that makes extensive use of this bad design of a sepeate
> > > "Time" field.  In the past, this application's database received its
> > > inserts/updates using ADO 2.x recordsets, rather than direct SQL
> > > statements.  ADO managed to store a time of day such that it
> > > *appeared* that SQL was only storing the time itself.  My guess is
> > > that the Update method of the recordset object sets the date to
> > > 12/30/1899 for any non-null datetime value which does not specify the
> > > date component.  I know now that ADO was confusing me all along, but
> > > it doesn't help my existing code which I am now attempting to migrate
> > > to ASP.NET, and update using ADO Command objects, rather than
> > > Recordsets.

> > > Okay, this is getting off-topic, my apologies.  Don't mean to start a
> > > debate, I think we're all saying similar things in different ways.
> > > Thanks for the replies.  Feel free to correct me on any of this, I'm
> > > always eager to learn more about the correct way to deal with SQL.

> > > David Johns



> > > > David,

> > > > As Luc has posted, SQL Server will always return both the date and
the
> > time.
> > > > The client application may choose to suppress the date or the time
> > portion
> > > > if it is set to certain values. Enterprise Manager, for example,
> > normally
> > > > suppresses the time portion if the time is set to midnight, but that
> is
> > a
> > > > function of EM, and not of SQL Server or of ADO.

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > This answer appears throughout Usenet, that datetime and
> smalldatetime
> > > > > store both a date and time, no matter what.  Technically, this is
> > > > > correct.  However, this is a way to achieve what the original
poster
> > > > > is looking for.  Believe it or not, if you put the date
"12/30/1899"
> > > > > in front of your time, SQL will display *only* the time for the
> value
> > > > > of the field.  It sounds bizarre, but try it!

> > > > > For an INSERT statement, try the following:

> > > > > "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM'
WHERE....."

> > > > > Examine the contents of the table.  You will find that the value
> > > > > "3:00:00 PM" is all that appears within the field.  I believe this
> is
> > > > > what people who are asking this question are looking for.

> > > > > As I mentioned, technically, the date is still there.  The problem
> is
> > > > > that when only a time is displayed, 12/30/1899 is the assumed
date.
> > > > > This means that given two datetime values:

> > > > > 1.  "6/4/2002 1:00:00 PM"
> > > > > 2.  "4:00:00 PM"

> > > > > SQL will actually evaluate value #2 as *less than* value #1,
because
> a
> > > > > date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do
> an
> > > > > ORDER BY on your time column, you must have the same date in front
> of
> > > > > the time in each row, or no date at all, but not a mixture, or
your
> > > > > sort will be quite wrong.

> > > > > Hope this helps.  It sure drove me crazy for a while!

> > > > > David Johns



> > > > > > Aamir,

> > > > > > > Is it possibleto save data in smalldatetime/datetime field in
> > HH:MM:SS
> > > > > > > AM/PM format only without a prefix date mm/dd/yyyy.

> > > > > > No. Both the smalldatetime and datetime data types store
**both**
> a
> > data
> > > >  and
> > > > > > a time.

> > > > > > You can retrieve the time only portion via:

> > > > > > select convert(char(8), getdate( ), 108)
> > > > > > select right(convert(varchar, getdate( ), 109), 14)

> > > > > > -------------------------------------------
> > > > > > BP Margolin
> > > > > > Please reply only to the newsgroups.
> > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
> etc.)
> > > >  which
> > > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > > Hi gurus,
> > > > > > > Is it possibleto save data in smalldatetime/datetime field in
> > HH:MM:SS
> > > > > > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > > > > > I am trying to save time value only in a stored procedure
which
> is
> > > > > > > originally a string value and I am casting it to smalldatetime
> > format
> > > > > > > before updating it to the table. But SQL by default puts
> > '1/1/1900' in
> > > > > > > front of my time. Does it have to do that. I want plain simple
> > time in
> > > > > > > my field, what is the way of doing it

> > > > > > > thanks

> > > > > > > aamir

 
 
 

Save time in hh:mm:ss AM/PM format

Post by David Joh » Fri, 07 Jun 2002 12:13:37


Luc, I agree entirely.  12/30/1899 is just plain bizarre in my book.
It's not even the last day of the year, for cripe's sake.  Nor is it
the base date for SQL Server, which would seem the most logical place
to truncate the date, if at all.

Ah, to have known about strange things like this three years ago, when
I thought that ADO was the way I would get away with having only
minimal knowledge of SQL.  What a flawed idea *that* turned out to be.
 Wonderful how they're both Microsoft products, and can't even agree
on something this simple.  You'd like to think that our friends at
Microsoft were attempting to follow some standard when they came up
with this one, but I'd hope that most of know better than that by now.

David Johns

"Luc" <xLucie...@hotmail.com> wrote in message <news:3cfe5aba$1@news.microsoft.com>...
> I checked and you're indeed right! For compatibility reasons a base date of
> 1/1/1900 is used. Of course it really is a bug if an application connecting
> to *SQL Server* displays no date since that's an incorrect client
> interpretation of the SQL Server datatype. But then again some people may
> rely on that behavior obviously :-(.

> As far as I can tell QA and OSQL work fine. VB/ADO grid control indeed
> truncates the time if it's midnight and truncates the date if it's
> 12/30/1899 (but not if it's 12/31/1899 which doesn't make sense).

> Luc

> "BP Margolin" <bpma...@attglobal.net> wrote in message
> news:3cfe3dde_2@news1.prserv.net...
> > Luc,

> > While it is true that the earliest date that SQL Server will accept in a
> > datetime data type is Jan 1, 1753, SQL Server uses a base date of Jan 1
> > 1900. Values after midnight Jan 1 1900 are (internally) positive values,
> > while values before midnight Jan 1 1900 are (internally) negative values.

> > However a number of other Microsoft products use different base dates ...
> > some with a year of 1899.

> > I wouldn't classify this behavior as a bug ... in ADO or elsewhere. It was
> > probably by design by the Microsoft developers, but admittedly the
>  behavior
> > is not one that I necessarily agree with   :-)

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> > can be cut and pasted into Query Analyzer is appreciated.

> > "Luc" <xLucie...@hotmail.com> wrote in message
> > news:3cfe3751@news.microsoft.com...
> > > Interestingly I believe that this is a bug in ADO. For a DATETIME 1899
>  it's
> > > not the first recognized date so if your application would need these
>  dates
> > > you have a problem (I believe 17xx is the first year accepted).

> > > "David Johns" <djo...@netarx.com> wrote in message
> > > news:28516c19.0206042046.407d6ec4@posting.google.com...
> > > ...
> > > > Unfortunately, my issue is a rather large, pre-existing web
> > > > application that makes extensive use of this bad design of a sepeate
> > > > "Time" field.  In the past, this application's database received its
> > > > inserts/updates using ADO 2.x recordsets, rather than direct SQL
> > > > statements.  ADO managed to store a time of day such that it
> > > > *appeared* that SQL was only storing the time itself.  My guess is
> > > > that the Update method of the recordset object sets the date to
> > > > 12/30/1899 for any non-null datetime value which does not specify the
> > > > date component.  I know now that ADO was confusing me all along, but
> > > > it doesn't help my existing code which I am now attempting to migrate
> > > > to ASP.NET, and update using ADO Command objects, rather than
> > > > Recordsets.

> > > > Okay, this is getting off-topic, my apologies.  Don't mean to start a
> > > > debate, I think we're all saying similar things in different ways.
> > > > Thanks for the replies.  Feel free to correct me on any of this, I'm
> > > > always eager to learn more about the correct way to deal with SQL.

> > > > David Johns

> > > > "BP Margolin" <bpma...@attglobal.net> wrote in message
>  <news:3cfd22cf_2@news1.prserv.net>...
> > > > > David,

> > > > > As Luc has posted, SQL Server will always return both the date and
>  the
>  time.
> > > > > The client application may choose to suppress the date or the time
>  portion
> > > > > if it is set to certain values. Enterprise Manager, for example,
>  normally
> > > > > suppresses the time portion if the time is set to midnight, but that
>  is
>  a
> > > > > function of EM, and not of SQL Server or of ADO.

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>  which
> > > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > "David Johns" <djo...@netarx.com> wrote in message
> > > > > news:28516c19.0206040926.ebdd6da@posting.google.com...
> > > > > > This answer appears throughout Usenet, that datetime and
>  smalldatetime
> > > > > > store both a date and time, no matter what.  Technically, this is
> > > > > > correct.  However, this is a way to achieve what the original
>  poster
> > > > > > is looking for.  Believe it or not, if you put the date
>  "12/30/1899"
> > > > > > in front of your time, SQL will display *only* the time for the
>  value
> > > > > > of the field.  It sounds bizarre, but try it!

> > > > > > For an INSERT statement, try the following:

> > > > > > "UPDATE TableName SET [DateTime] = '12/30/1899 3:00 PM'
>  WHERE....."

> > > > > > Examine the contents of the table.  You will find that the value
> > > > > > "3:00:00 PM" is all that appears within the field.  I believe this
>  is
> > > > > > what people who are asking this question are looking for.

> > > > > > As I mentioned, technically, the date is still there.  The problem
>  is
> > > > > > that when only a time is displayed, 12/30/1899 is the assumed
>  date.
> > > > > > This means that given two datetime values:

> > > > > > 1.  "6/4/2002 1:00:00 PM"
> > > > > > 2.  "4:00:00 PM"

> > > > > > SQL will actually evaluate value #2 as *less than* value #1,
>  because
>  a
> > > > > > date of 12/30/1899 is assumed.  Weird, huh?  So, if you want to do
>  an
> > > > > > ORDER BY on your time column, you must have the same date in front
>  of
> > > > > > the time in each row, or no date at all, but not a mixture, or
>  your
> > > > > > sort will be quite wrong.

> > > > > > Hope this helps.  It sure drove me crazy for a while!

> > > > > > David Johns

> > > > > > "BP Margolin" <bpma...@attglobal.net> wrote in message
>  <news:3cf7e3c8_4@news1.prserv.net>...
> > > > > > > Aamir,

> > > > > > > > Is it possibleto save data in smalldatetime/datetime field in
>  HH:MM:SS
> > > > > > > > AM/PM format only without a prefix date mm/dd/yyyy.

> > > > > > > No. Both the smalldatetime and datetime data types store
>  **both**
>  a
>  data
>  and
> > > > > > > a time.

> > > > > > > You can retrieve the time only portion via:

> > > > > > > select convert(char(8), getdate( ), 108)
> > > > > > > select right(convert(varchar, getdate( ), 109), 14)

> > > > > > > -------------------------------------------
> > > > > > > BP Margolin
> > > > > > > Please reply only to the newsgroups.
> > > > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
>  etc.)
>  which
> > > > > > > can be cut and pasted into Query Analyzer is appreciated.

> > > > > > > "Aamir" <ghan...@bigfoot.com> wrote in message
> > > > > > > news:4a99b4d7.0205311214.2332e166@posting.google.com...
> > > > > > > > Hi gurus,
> > > > > > > > Is it possibleto save data in smalldatetime/datetime field in
>  HH:MM:SS
> > > > > > > > AM/PM format only without a prefix date mm/dd/yyyy.
> > > > > > > > I am trying to save time value only in a stored procedure
>  which
>  is
> > > > > > > > originally a string value and I am casting it to smalldatetime
>  format
> > > > > > > > before updating it to the table. But SQL by default puts
>  '1/1/1900' in
> > > > > > > > front of my time. Does it have to do that. I want plain simple
>  time in
> > > > > > > > my field, what is the way of doing it

> > > > > > > > thanks

> > > > > > > > aamir

 
 
 

1. Save time in hh:mm:ss AM/PM format

Hi gurus,
Is it possibleto save data in smalldatetime/datetime field in HH:MM:SS
AM/PM format only without a prefix date mm/dd/yyyy.
I am trying to save time value only in a stored procedure which is
originally a string value and I am casting it to smalldatetime format
before updating it to the table. But SQL by default puts '1/1/1900' in
front of my time. Does it have to do that. I want plain simple time in
my field, what is the way of doing it

thanks

aamir

2. Returned mail: warning: cannot send message for 4 hours

3. Convert mm/dd/yyyy hh:mm:ss AM/PM to mm/dd/yyyy format

4. This is Too Easy!

5. Display Time Only (hh:mm:ss AM - format)

6. DB2 Client Response File Install Problems

7. Dates in dd/mm/yyy hh:mm:ss format

8. Screen Conversion

9. converting msdb sysjobhistory time to hh:mm:ss format

10. How to get hh:mm AM(or PM)

11. Format seconds to HH:MM:SS?

12. Formatting sum of seconds into hh:mm:ss

13. ConvertSeconds to HH:MM:SS format