Converting DateTime to mm/dd/yyyy in results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting DateTime to mm/dd/yyyy in results

Author

Topic

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-02 : 11:47:04

Having problems with DateTime. Need for the results to show mm/dd/yyyy instead of the DateTime. Any Ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-02 : 11:53:10

look up convert in BOL. note that with this you change the datatype from datetime to varchar.
ideally formatting should be done in front end.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-02 : 11:56:26

Will this only show up in the results?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-07-02 : 12:04:58

If I understand your question correctly, yes, using the CONVERT will only change the result but not how the data is actually stored in the database (that is assuming you are using DATETIME as the data type).

SQL Server Helper
http://www.sql-server-helper.com

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-02 : 12:06:11

Yes, DateTime is the Data Type.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-02 : 12:11:26

It will not change your table data type but convert() will change the data type of the result available to the front end. !

KH
[spoiler]Time is always against us[/spoiler]

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-02 : 12:21:56

Keep getting syntax error for 'Convert'

SELECT [COMP_SKU_CONDENSED]
,[COMP_PRICE]
,[DICT_STATUS]
,[COLLECT_DATE]
,[COMP_CODE_REF]
FROM [XREF_Prod].[dbo].[COMPETITOR_ITEMS]
where [COMP_CODE_REF] = 'FNL'
and [COLLECT_DATE] >= '6/1/2006'
and [COLLECT_DATE] <= '6/1/2007'
CONVERT ([COLLECT_DATE] AS [MM/DD/YYYY])

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-02 : 12:26:46

[code]SELECT [COMP_SKU_CONDENSED]
,[COMP_PRICE]
,[DICT_STATUS]
,CONVERT(VARCHAR(10), [COLLECT_DATE], 101)
,[COMP_CODE_REF]
FROM [XREF_Prod].[dbo].[COMPETITOR_ITEMS]
where [COMP_CODE_REF] = 'FNL'
and [COLLECT_DATE] >= '20060601'
and [COLLECT_DATE] <= '20070601'[/code]

KH
[spoiler]Time is always against us[/spoiler]

bduncs2001
Starting Member

17 Posts

Posted - 2007-07-02 : 12:31:38

Brilliant...Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources