Default Date Format used by Mssql

Hi there,

What is the default date format used by transact sql? Would YYYY-MM-DD HOUR:MIN:SECS format work on Mssql?

I am working on a project that needs to work with atleast two databases (Mysql/Mssql). I use the above date format and while it works perfectly on all Mysql databases, it gives me trouble in some Mssql setups.

Most of the trouble arises when I am doing INSERT or SELECT queries.

How do I handle this? Is there some way that I can tell Mssql that I am using the yyyy-mm-dd format or should I find out what format that particular mssql is using and adopt it?YYYY-MM-DD hh:mm:ss should work fine with mssql. that's basically the ODBC format.

See http://msdn2.microsoft.com/en-us/library/ms187928.aspx for a list of all the different date formats in sql server.|||I've never had the ISO standard temporal format (YYYY-MM-DD HH:MM:SS.TTT) give me trouble with Microsoft SQL, that is actually the preferred format for dates and times. The only thing I've had trouble with Microsoft-SQL handling DATETIME values in that time format was because MS-SQL can only resolve time down to 3 ms so it sees all three of the following times as identical:

2006-08-07 06:05:04.000
2006-08-07 06:05:04.001
2006-08-07 06:05:04.002

If you are using SMALLDATETIME values, things get more interesting quickly, since those are only accurate to the minute. That might be a whole different issue.

-PatP|||One of my clients use Mssql and her server uses the YYYY-DD-MM date format. I had to change the date formatting in the code to make it work for her.

I would really need to know whether this is an issue in some mssql servers.|||No, I don't know of ANY condition under which MS-SQL 7.0 or later versions have any problem interpreting dates formatted as YYYY-MM-DD as long as the date is valid for the datatype you are using.

As I don't know what made you think you needed to change the date formatting, all I can do is say that the date format isn't the problem, something else is.

-PatP|||Yeah, When I changed my date format, the queries worked. So, the problem is really with the format.|||Maybe I'm not making this clear, but the ISO string format you are using for the date is NOT the problem. I don't know what is the problem, but the ISO format isn't it.

-PatP

default column value

as current time..
how can i set that in mssql 2005 ?Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
CREATE TABLE test_defaults
(keycol smallint,
process_id smallint DEFAULT @.@.SPID, --Preferred default definition
date_ins datetime DEFAULT getdate(), --Preferred default definition
mathcol smallint DEFAULT 10 * 2, --Preferred default definition
char1 char(3),
char2 char(3) DEFAULT 'xyz') --Preferred default definition;
GO
HTH. Ryan
"gary" <admin@.newsgroup.com.hk> wrote in message
news:eHFDHarEGHA.3100@.tk2msftngp13.phx.gbl...
> as current time..
> how can i set that in mssql 2005 ?
>|||Note that naming constraints is considered good practice. Sooner or later yo
u will want to change
some defaults, and if you don't know the name, you have to look up the auto-
generated name in the
system tables. This makes implementation of such scripts a mess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uwM3JhrEGHA.916@.TK2MSFTNGP10.phx.gbl...
> Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
> CREATE TABLE test_defaults
> (keycol smallint,
> process_id smallint DEFAULT @.@.SPID, --Preferred default definition
> date_ins datetime DEFAULT getdate(), --Preferred default definition
> mathcol smallint DEFAULT 10 * 2, --Preferred default definition
> char1 char(3),
> char2 char(3) DEFAULT 'xyz') --Preferred default definition;
> GO
>
> --
> HTH. Ryan
>
> "gary" <admin@.newsgroup.com.hk> wrote in message news:eHFDHarEGHA.3100@.tk2
msftngp13.phx.gbl...
>

Debug Stored Proc's in MSSQL 2005

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

You can not debug Sp from SQL Server enviornment in 2005.... use Visual Studio

Ref :http://msdn2.microsoft.com/en-us/library/zxsa8hkf(VS.80).aspx

Madhu

|||

Thanks, I had seen that one before but didn't want to write it as managed code, so I stop short looking further. I did however, follow a few more links at the bottom and it brought me to the article:

How To: Step into an Object Using Server Explorer
http://msdn2.microsoft.com/en-us/library/ms165041(VS.80).aspx

Thanks!

Debug Stored Procs in MSSQL 2005

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

I found the answer to my question.

How To: Step into an Object Using Server Explorer
http://msdn2.microsoft.com/en-us/library/ms165041(VS.80).aspx