|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > How to insert datetime field
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to insert datetime field
|
|
| Felipe Barbiere 2002-11-20, 5:23 am |
| I'm having a problem with datetime field in SQL Server.
I want to insert only time, but always a date is inserted.
Example: '01/01/1900 04:00:00'.
How to insert only the time? ('04:00:00')
Thanks.
| |
| Tibor Karaszi 2002-11-20, 5:23 am |
| The name of the datatypes is "datetime". This implies that SQL Server *always* store both date
and time. There is no "time" datatypes in SQL Server. If you omit the date, you get the standard
value 1900-01-01. What you should do is to not show the date in the client application. Or use
CONVERT to convert the datetime value to a suitable formatted string in your SQL query.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Felipe Barbiere" <felipe.barbiere@power.alstom.com> wrote in message
news:f5a0670b.0211200256.6abe6ead@posting.google.com...
> I'm having a problem with datetime field in SQL Server.
> I want to insert only time, but always a date is inserted.
> Example: '01/01/1900 04:00:00'.
>
> How to insert only the time? ('04:00:00')
>
>
> Thanks.
| |
|
| I'm not really sure how this might help you, but using a date of
12-30-1899 will in Enterprise Manager produce just the time portion of
the field. Query Analyzer gives back the full date & time. Haven't
tried various data providers. I suggest choosing one date and using
it in every row of the column, that way if a date is different you
know something went wrong. Then filter Dates @ Query or App level as
suggested in a previous post.
felipe.barbiere@power.alstom.com (Felipe Barbiere) wrote in message news:<f5a0670b.0211200256.6abe6ead@posting.google.com>...
> I'm having a problem with datetime field in SQL Server.
> I want to insert only time, but always a date is inserted.
> Example: '01/01/1900 04:00:00'.
>
> How to insert only the time? ('04:00:00')
>
>
> Thanks.
| |
| James Hunter Ross 2002-11-20, 4:23 pm |
| Like the others say, date/time are both stored in a SQL Server datetime
field. But, sometimes you need to refer to a time of day generically, like
a list of store closing times. Store A might close at 5pm while store B
closes at 6:30pm, or whatever. You could use datetime to hold this data,
but you might instead consider using an INT such as 1700 for 5pm 1830 for
6:30pm. In fact, MS uses encoded integers in its own job scheduling
subsystem of SQL Server for both dates and times. Check out
MSDB..sysjobschedules. I've worked with this model quite a bit, (to create
our own schedules and such), and find it works well.
James
"Felipe Barbiere" <felipe.barbiere@power.alstom.com> wrote in message
news:f5a0670b.0211200256.6abe6ead@posting.google.com...
> I'm having a problem with datetime field in SQL Server.
> I want to insert only time, but always a date is inserted.
> Example: '01/01/1900 04:00:00'.
>
> How to insert only the time? ('04:00:00')
>
>
> Thanks.
|
|
|
|
|