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

General SQL Server Forums

New to SQL Server Programming

Date Format Q

Author

Topic

Dagmd
Starting Member

14 Posts

Posted - 2007-05-10 : 06:10:55

Hi all,

I'm attempting to parse a datetime stamp field which in raw -
returns as:- "2004-04-09 00:00:00.000"

I would like format into "Apr 04" and I have been currently using DATENAME(mm) so far, however I'm just wondering if further cmds can format to this?

Thanks

Pat

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 06:18:47

Select cast(Convert(smalldatetime,'2004-04-09 00:00:00.000') as varchar(20))

Chirag

http://chirikworld.blogspot.com/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 06:29:37

U can specify the style u want

Select convert(varchar(20),Convert(smalldatetime,'2004-04-09 00:00:00.000'), 113)

113 - 09 Apr 2004 00:00:00
110 - Apr 9 2004 12:00AM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 06:29:49

quote:
Originally posted by Dagmd

Hi all,

I'm attempting to parse a datetime stamp field which in raw -
returns as:- "2004-04-09 00:00:00.000"

I would like format into "Apr 04" and I have been currently using DATENAME(mm) so far, however I'm just wondering if further cmds can format to this?

Thanks

Pat

Where do you want to show formatted data?
Use query to get date and format it in the way you want in front end

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 06:32:58

quote:
Originally posted by pbguy

U can specify the style u want

Select convert(varchar(20),Convert(smalldatetime,'2004-04-09 00:00:00.000'), 113)

113 - 09 Apr 2004 00:00:00
110 - Apr 9 2004 12:00AM

Should be used only if data are not shown in front end

Madhivanan

Failing to plan is Planning to fail

Dagmd
Starting Member

14 Posts

Posted - 2007-05-10 : 07:37:26

Thanks for replies; I think using convert should cover the requirement.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 07:49:39

quote:
Originally posted by Dagmd

Thanks for replies; I think using convert should cover the requirement.

I hope you read my replies

Madhivanan

Failing to plan is Planning to fail

Dagmd
Starting Member

14 Posts

Posted - 2007-05-10 : 10:00:28

quote:
Originally posted by madhivanan

quote:
Originally posted by Dagmd

Hi all,

I'm attempting to parse a datetime stamp field which in raw -
returns as:- "2004-04-09 00:00:00.000"

I would like format into "Apr 04" and I have been currently using DATENAME(mm) so far, however I'm just wondering if further cmds can format to this?

Thanks

Pat

Where do you want to show formatted data?
Use query to get date and format it in the way you want in front end

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan,
the plan is to show the formated date in a string field in a web browser.
thanks
Pat

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 10:06:10

quote:

Hi Madhivanan,
the plan is to show the formated date in a string field in a web browser.
thanks
Pat

Then, why dont you simply use Format function there?
Just return datetime and format it when displaying

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-10 : 10:53:25

Madhivanan's absolutely right. CONVERTing that nice DateTime to a VARCHAR at the database means that you web application has its hands tied and cannot format or work with the datetime; it is simple a string of text. Always return raw data to your applications from your database and let your applications format that data. Never convert dates and bits and money and decimal datatypes to VARCHAR at the database layer unless you have absolutely no other options.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-10 : 10:54:22

see: http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

for more on this.

- Jeff
http://weblogs.sqlteam.com/JeffS

Dagmd
Starting Member

14 Posts

Posted - 2007-05-10 : 11:35:48

quote:
Originally posted by jsmith8858

Madhivanan's absolutely right. CONVERTing that nice DateTime to a VARCHAR at the database means that you web application has its hands tied and cannot format or work with the datetime; it is simple a string of text. Always return raw data to your applications from your database and let your applications format that data. Never convert dates and bits and money and decimal datatypes to VARCHAR at the database layer unless you have absolutely no other options.

- Jeff
http://weblogs.sqlteam.com/JeffS

Hi Jeff, (and Madhivanan)

That's a fair point, however in this circumstance - we only need to store the date - not perform any functions on it - thus storing as a string...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-10 : 11:42:26

What do you mean, "store the date"? Do you mean "display the date"? Where are you storing it? If you are storing it somewhere, all the more reason to keep it in the proper datatype.

The "correct" way to do this -- formatting at your presentation layer -- is also the shortest, quickest, easiest and most flexible way to do it. There are literally no cons to taking the correct approach here. I can understand being reluctant if I tell you that "best practices are to have 10 error handlers, and to inherit from a base class, and to add 25 lines of code, etc", but when the best practice is also the shortest, easiest, fastest and most flexible way to do it, I just don't understand why people resist. Oh well ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Dagmd
Starting Member

14 Posts

Posted - 2007-05-10 : 13:30:18

quote:
Originally posted by jsmith8858

What do you mean, "store the date"? Do you mean "display the date"? Where are you storing it? If you are storing it somewhere, all the more reason to keep it in the proper datatype.

The "correct" way to do this -- formatting at your presentation layer -- is also the shortest, quickest, easiest and most flexible way to do it. There are literally no cons to taking the correct approach here. I can understand being reluctant if I tell you that "best practices are to have 10 error handlers, and to inherit from a base class, and to add 25 lines of code, etc", but when the best practice is also the shortest, easiest, fastest and most flexible way to do it, I just don't understand why people resist. Oh well ....

- Jeff
http://weblogs.sqlteam.com/JeffS

No I agree Jeff - the correct way is to remain a date as a date, but I am storing the date only as a reference field in a string.

I have a 3 month representation and a year from below and I actually need to convert the latter to a string so that I can concatenate in SQL Server.

Select convert(varchar(3),Convert(smalldatetime, dateField))
,datepart(yy, dateField)
from whateverTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 03:47:25

All you need is

Format(Rs("datecol"),"MMM")
Format(Rs("datecol"),"YYYY")

where Rs is the recordset that returns data to the client

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 03:59:28

quote:
Originally posted by jsmith8858

see: http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

for more on this.

- Jeff
http://weblogs.sqlteam.com/JeffS

Thats MUST read article
I think you must have posted it long time back

Madhivanan

Failing to plan is Planning to fail

Dagmd
Starting Member

14 Posts

Posted - 2007-05-11 : 07:58:16

quote:
Originally posted by madhivanan

All you need is

Format(Rs("datecol"),"MMM")
Format(Rs("datecol"),"YYYY")

where Rs is the recordset that returns data to the client

Madhivanan

Failing to plan is Planning to fail

Thanks. Do you know how I can place the result of a SELECT into the "Rs" part of the above 'format' statement?

Cheers

vipinM
Starting Member

9 Posts

Posted - 2007-05-11 : 08:27:48

Try this one

select substring(convert(varchar,convert(datetime,'2004-04-09 00:00:00.000'),6),4,6)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-11 : 08:38:58

quote:
Originally posted by vipinM

Try this one

select substring(convert(varchar,convert(datetime,'2004-04-09 00:00:00.000'),6),4,6)

vipinM -- the article I linked to earlier in this thread was written especially for you, it would seem. Please check it out!

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-11 : 08:41:10

DagM - Let's step back a minute. Somehow, you have a SQL statement that returns data. Then, you are taking that data and displaying it on a web page, as you mentioned. How are you displaying the data on your web page? Are you using ASP, ASP.NET, PHP, or something else? If you are using ASP.NET, are you using a DataGrid, Repeater, Response.Write(), etc ?

What we've been trying to say is just return the date, and then on your web page you can simply format it as "MMM yy" or anyway that you want, nice and easy and quick. But we can't tell you for sure where and how to do the formatting if we don't know how you are outputting the results.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 09:37:57

quote:
Originally posted by vipinM

Try this one

select substring(convert(varchar,convert(datetime,'2004-04-09 00:00:00.000'),6),4,6)

I dont know if you actually read all the replies or you read the question only and posted reply without read other's

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources