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 Date Format

Hello All
In oracle we can specify the default date format for the datebase
connection, is there a way to specify the default date format and language
for SQL Server database connections. I am using DB-Library for C to
communicate to SQL Server from MS Visual C++.
Hope to get reply soon
Thanks
FaisalSET DATEFORMAT should work
regards,
Harshal.
"Faisal Mansoor" <fmansoor@.softpak.com> wrote in message
news:ukd6ezqLFHA.1180@.TK2MSFTNGP14.phx.gbl...
> Hello All
> In oracle we can specify the default date format for the datebase
> connection, is there a way to specify the default date format and language
> for SQL Server database connections. I am using DB-Library for C to
> communicate to SQL Server from MS Visual C++.
> Hope to get reply soon
> Thanks
> Faisal
>|||Only for input, not for output. Default date format for input is inherited f
rom the login, but can
be overridden by SET DATEFORMAT. I suggest you read
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Faisal Mansoor" <fmansoor@.softpak.com> wrote in message
news:ukd6ezqLFHA.1180@.TK2MSFTNGP14.phx.gbl...
> Hello All
> In oracle we can specify the default date format for the datebase
> connection, is there a way to specify the default date format and language
> for SQL Server database connections. I am using DB-Library for C to
> communicate to SQL Server from MS Visual C++.
> Hope to get reply soon
> Thanks
> Faisal
>

Default Allocation Size

In setting up the Data directory for SQL 2000 Im at at the
format partition. The default partition size is
recommended for general use but it says it depends on the
size of the disk.
We are connected to a SAN that is set to 64k stripe set.
Do we need to also set the format of the partition to the
same 64k or will default take care of that (my thinking).
Rob Thiebeau64k is good... you will be fine.!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob Thiebeau" <anonymous@.discussions.microsoft.com> wrote in message
news:0afa01c3db7b$3bbf50c0$a501280a@.phx.gbl...
> In setting up the Data directory for SQL 2000 Im at at the
> format partition. The default partition size is
> recommended for general use but it says it depends on the
> size of the disk.
> We are connected to a SAN that is set to 64k stripe set.
> Do we need to also set the format of the partition to the
> same 64k or will default take care of that (my thinking).
> Rob Thiebeausql

decimals

I need to learn to format text boxes in the report design to not have any decimals but to have commas when needed.

Any help is appreciated.

I dont understand very well your question but to remove decimals you only have to convert to int

=Cint(Fields!field.value)

|||

You can just set the Format property of the textbox using Standard Format Strings.

For example c2 is Currency with 2 decimals. I think what you want is n. Check out the link so you can decide for yourself the format you are looking for.

If the Standard formats don't do the trick check out Custom Formatting.

|||

Thanks everyone! I am using numbers and found that I can use n0 or n + zero to make the textbox display as a number with 0 prescision.

Take care.

Decimal formatting

Hi,

we have a decimal data field and we want to format it in the correct langauge specific formatting (decimal point is a "comma" in Germany...). We tried different format strings but we always get a decimal point (really a point). Is there any way to format a decimal the way it's defined in the Windows language settings? Always with 2 digits after the point (so exactly like the currency without the currency symbol)?

Thanks,

Thomas

The "Report" element has a Language property. Make sure the Language property is set to "DE-de" or e.g. to =User!Language (to run the report with the language set on the requesting client).

Then, assuming the field value has a numeric datatype (and is not an already formatted string), you can e.g. set the format property on the textbox to N2 to get the desired formatting.

Check these links for more information on format strings:
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp

-- Robert

|||

Robert,

Thanks... Works perfectly...

Decimal data type

If I create a column with data type Decimal(10,4), when a data is being
stored in that column, will it format the data to be Decimal(10,4) ?
For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
When I tried it, it stays at 1.5.
Thanks.fniles wrote:

> If I create a column with data type Decimal(10,4), when a data is
> being stored in that column, will it format the data to be
> Decimal(10,4) ? For example: if I enter 1.5, will it be 1.5000, or
> it will stay as 1.5 ? When I tried it, it stays at 1.5.
No it will always stay 1.5 because 1.5000 is just a string
representation of a number. You best handle that in your application.
HTH,
Stijn Verrept.|||"fniles" <fniles@.pfmail.com> wrote in message
news:umjqImN$FHA.2520@.TK2MSFTNGP15.phx.gbl...
> If I create a column with data type Decimal(10,4), when a data is being
> stored in that column, will it format the data to be Decimal(10,4) ?
> For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
> When I tried it, it stays at 1.5.
> Thanks.
I'm going to guess that you're looking at the data in Enterprise Manager.
Go to Query Analyser and Select from that table.
In my SQL Server, EM formats it as 1.5 but in QA, 1.500.|||numbers are not stored with a format. format is a matter of display -
e.g., in Query Analyzer look in Tools \ Options \ Connections - see if
Use regional settings is checked.
fniles wrote:
> If I create a column with data type Decimal(10,4), when a data is being
> stored in that column, will it format the data to be Decimal(10,4) ?
> For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
> When I tried it, it stays at 1.5.
> Thanks.
>|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e3yclwN$FHA.4028@.tk2msftngp13.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:umjqImN$FHA.2520@.TK2MSFTNGP15.phx.gbl...
> I'm going to guess that you're looking at the data in Enterprise Manager.
> Go to Query Analyser and Select from that table.
> In my SQL Server, EM formats it as 1.5 but in QA, 1.500.
Add an extra 0 to that output.
One rule to remember, SQL does not FORMAT data in tables, it stores data.
It will make your life simpler if you remember that rule.