Date Format - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Date Format

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-10 : 16:29:11

what is the propper way to convert a datetime field to a format of my choice "I.e. yyyymmdd"

I know how to use the
***
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

convert(varchar,DateColumn,101)
**

but if I just want yyyymmdd what is the best way to accomplish this.

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 16:40:45

http://www.aspfaq.com/show.asp?id=2460

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-10 : 16:48:40

If it's in the SELECT portion, then you can do it using CONVERT. The best way to do it though is in the presentation layer, which is the application.

If it's in the WHERE clause, then you can't use CONVERT as it won't be able to use an index. Here's an example of how not to do it:

SELECT ...
FROM YourTable
WHERE CONVERT(CONVERT(varchar(10), YourDateTimeColumn, 101) = CONVERT(varchar(10), GETDATE(), 101)

The above can't make use of an index as the YourDateTimeColumn isn't isolated on one side of the predicate. Here's how it should be done:

SELECT ...
FROM YourTable
WHERE YourDateTimeColumn >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
AND YourDateTimeColumn < DATEADD(Day, DATEDIFF(Day, 0, GETDATE())+1, 0)

Tara Kizer

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-10 : 16:58:01

Thanks Srinika

That'll work

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-11 : 12:49:23

http://weblogs.sqlteam.com/derrickl/archive/2005/01/08/3959.aspx

But use DateTime datatype and do formation at front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources