datetime format

[beti]

I have a question regarding the datetime format.
I have a field with datatype of datetime, how I can change to format when I retrieve from database from (yyyy-mm-dd hh:mi:ss) to (yyyy-mm-dd)? I have tried the Cast function, cast(mydate as char(10)) but this will change the format to (Mon dd yyyy) where I wanted (yyyy-MM-dd).
Can anyone answer my question??

 

[mukund]

Hi Beti,
use the Convert function. For your requirement use the following style of Convert function

Select Convert(varchar(10), datefield, 126) as somedate from
TableName

Replace the somedate, datefield and TableName with appropriate values.

-Mukund.

 

[mwolf00]

FYI:

Using CAST:
CAST(expression AS data_type)
Using CONVERT:

CONVERT (data_type[(length)], expression [, style])

Arguments

expression

Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.

data_type

Is the target system-supplied data type. User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format you want when converting datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char
, varchar, nchar, or nvarchar data types).
In the table, the two columns on the left represent the style values for datetime
or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

(yy) (yyyy) Standard Input/Output**
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).** Input when converting to datetime; Output when converting to character data.
Important By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.
This table shows the style values for float or real conversion to character data.

Value Output
0 (the default) 6 digits maximum. Use in scientific notation, when appropriate.
1 Always 8 digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.
In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Value Output
0 (the default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
Return Types

Returns the same value as data type 0.

Remarks

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types. Get the Best Answers! faq333-2924
Merry Christmas!
mikewolf@tst-us.com
[santa2]

 

[tlbroadbent]

Mike,

I recommend posting the title of a BOL subject or a link to the T-SQL reference on the MSDN website rather than copying and pasting the entire reference in a thread. That makes the thread easier to read and avoids any potential problems with posting too much copyrighted material. It also helps people become familiar with the resources and less dependent on Tek-Tips for answers that can be found in the manual.

Cast and Convert in SQL BOL T-SQL Reference at MSDN:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

---------------------------------------------

Beti,

Check out my page on "Handling Dates in SQL Server" at

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[mwolf00]

You're right Terry. I'll try to remember that next time. Get the Best Answers! faq333-2924
Merry Christmas!
mikewolf@tst-us.com
[santa2]