Getting Time in SmallDatetime  
Author Message
NWtst





PostPosted: Wed Feb 11 17:12:47 CST 2004 Top

SQL Server Developer >> Getting Time in SmallDatetime

I'm working on getting some other software to output a smalldatetime into Sql. This software is capable of outputting a string. It seems that if I just have a date, or if I have a date and a time with a space between them, SQL will accept the date, but will chop off the time. If I try adding single quotes around the date and time, it tells me I have an invalid datetime format. I've also tried a colon between the date and time to no avail. Is there something else I need to do to get it to take both the date and time?
Thanks

SQL Server86  
 
 
J





PostPosted: Wed Feb 11 17:12:47 CST 2004 Top

SQL Server Developer >> Getting Time in SmallDatetime kbarz

SQL Server should properly cast a string as a datetime, but try using a
standard format in the string, like yyyy-dd-mm hh:mm:ss.ppp.

Joe



> I'm working on getting some other software to output a smalldatetime into
Sql. This software is capable of outputting a string. It seems that if I
just have a date, or if I have a date and a time with a space between them,
SQL will accept the date, but will chop off the time. If I try adding
single quotes around the date and time, it tells me I have an invalid
datetime format. I've also tried a colon between the date and time to no
avail. Is there something else I need to do to get it to take both the date
and time?
> Thanks


 
 
Erland





PostPosted: Wed Feb 11 17:21:14 CST 2004 Top

SQL Server Developer >> Getting Time in SmallDatetime
> I'm working on getting some other software to output a smalldatetime
> into Sql. This software is capable of outputting a string. It seems
> that if I just have a date, or if I have a date and a time with a space
> between them, SQL will accept the date, but will chop off the time. If
> I try adding single quotes around the date and time, it tells me I have
> an invalid datetime format. I've also tried a colon between the date
> and time to no avail. Is there something else I need to do to get it to
> take both the date and time?

Without seeing your code, it is a bit difficult to say what is going on.
But it sounds that SQL does not get the right thing to work with
from the start.

SQL Server can interpret a whole range of date and time formats, and almost
all of these formats have a space between date and time, for instance:

'20031212 17:28'
'9/1/2003 16:12'
'Oct 12 2003 7:23 am'
'16/03/2004 1:32 pm'
'2003-04-29 12:32'

The catch though, is that the interpretation of many of thes format depends
on language and datetime settings. Of the above, only the first is safe.

There is however one format with another delimiter than space:

'2003-12-24T15:00:00'

This format is always free from variation due dateformat settings. Note
here that you need to specify the seconds; they are not optional in this
format.

--


Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp