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

Transact-SQL (2005)

Date format

Author

Topic

shwelch
Starting Member

33 Posts

Posted - 2007-08-31 : 05:56:16

Hi,

I have a field that I am pulling out converted to varchar and returns date in format mm/dd/yyyy. I am using style 102 I think that of course adds a leading 0 to month for 1-9, and also adds a leading zero to the day if it is 0-9. Is there a way to get format the month and day without the leading zeros?!?

I have tried doing it in an asp.net gridview on row data bind, but getting conversion errors from string/date. Any ideas on how to handle this directly in the sql so It arrives in the correct format to the gridview?

Thanks!

sbalaji
Starting Member

48 Posts

Posted - 2007-08-31 : 06:58:20

I dont know whether this is the best way

select convert(varchar(2),DATEPART (mm,getdate())) + '/' +
convert(varchar(2),DATEPART (dd,getdate())) + '/' +
convert(varchar(4),DATEPART (yyyy,getdate()))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-31 : 07:13:28

Format(rs("datecol"),"m/d/yyyy")

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 07:16:03

SELECT STUFF(REPLACE('/' + CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101), '/0', '/'), 1, 1, '')

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-31 : 08:34:48

You said that you are using a DataView yet people are still trying to tell you to do it in T-SQL. bad advice.

Just return a DATETIME value from your stored proc. Not something converted to a VARCHAR formatted that looks like a date, but a simple, exact DATETIME value.

Then, you can simply apply a format like "m/d/y" or whatever you want in ASP.NET. It's very easy, very quick, very short, and the way it is supposed to be done.

If you are getting errors in ASP.NET saying it is having trouble formatting your dates, it is because you are not returning DATETIME values from your stored procedure -- you are returning VARCHARs or STRINGS. If you are not using DATETIME data type in your columns to store your data, you have a really bad database design -- and it should be fixed.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 08:36:54

He might not know how to format the result, thus asking here for a t-sql solution?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-31 : 08:45:52

quote:
Originally posted by Peso

He might not know how to format the result, thus asking here for a t-sql solution?

E 12�05.25"
N 56�39.16"

Then he should learn how to format the result in ASP.NET. It takes two seconds to learn, its intuitive, it's short, you can specify simple to learn and write and edit format strings like "M/d/y", it's flexible, it's the right way to do it, it's the way DataGridViews and DataGrids and everything else in ASP.NET is *designed* to work. You can sort, compare, format and calculate the values if you return native DATETIME values, which you cannot do as a VARCHAR or a string. It requires no T-SQL code changes at all, no converts, no formatting, no issues with regional settings.

Are we really going to debate this?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 08:54:27

No, I agree 100% with you!

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-31 : 08:57:10

<<
Are we really going to debate this?
>>

Probably yes

Becuase I rarely see people suggesting doing formation at front end
AFAIK, only Me and You are suggesting here

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-31 : 09:37:21

Yeah, it kind of sucks. People looking for help just see a quick "cut and paste" answer, even though it's a bad one and complicated and hard to understand, so they use it and move on, and ignore the better advice of doing things the simple, easy way, because that takes a little bit of initial effort and learning.

If I am confused and need help, and I ask I question, and I am given two options:

(A) Just cut and paste this code which is long, complicated, inflexible and difficult to maintain, which you won't understand, but does appear to work

or

(B) Learn a best practice will take about 4-5 minutes and then you can simply use format strings like "m/d/yyyy"

I will always choose B. But most people? All they want is (A). they don't want to understand, or improve. They just want to cut and paste and "make it work right away!". The problem is that we propagate that here by providing those bad option (A) solutions all the time, thus decreasing the overall understanding of SQL and programming in general and promoting "worst" practices instead of best practices. It's definitely a pet peeve of mine here at SQLTeam, especially when people giving answers should know better.

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-31 : 10:15:18

quote:
Originally posted by jsmith8858

Yeah, it kind of sucks. People looking for help just see a quick "cut and paste" answer, even though it's a bad one and complicated and hard to understand, so they use it and move on, and ignore the better advice of doing things the simple, easy way, because that takes a little bit of initial effort and learning.

If I am confused and need help, and I ask I question, and I am given two options:

(A) Just cut and paste this code which is long, complicated, inflexible and difficult to maintain, which you won't understand, but does appear to work

or

(B) Learn a best practice will take about 4-5 minutes and then you can simply use format strings like "m/d/yyyy"

I will always choose B. But most people? All they want is (A). they don't want to understand, or improve. They just want to cut and paste and "make it work right away!". The problem is that we propagate that here by providing those bad option (A) solutions all the time, thus decreasing the overall understanding of SQL and programming in general and promoting "worst" practices instead of best practices. It's definitely a pet peeve of mine here at SQLTeam, especially when people giving answers should know better.

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

While I agree with you I think it would be unfair not to share how to do something even if it is not the best way. I think it is definitely important to point out that it is not the best way to do it, but it is good for the purpose of learning to know how things can be done in the backend as well.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-31 : 10:25:30

<<
but it is good for the purpose of learning to know how things can be done in the backend as well.
>>

Yes I agree. But mostly after learning how to do it in query, everyone tries to do it at backend as usual always without knowing it should be done at front end if formatted dates are shown there

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-31 : 10:34:18

quote:
Originally posted by Zoroaster

quote:
Originally posted by jsmith8858

Yeah, it kind of sucks. People looking for help just see a quick "cut and paste" answer, even though it's a bad one and complicated and hard to understand, so they use it and move on, and ignore the better advice of doing things the simple, easy way, because that takes a little bit of initial effort and learning.

If I am confused and need help, and I ask I question, and I am given two options:

(A) Just cut and paste this code which is long, complicated, inflexible and difficult to maintain, which you won't understand, but does appear to work

or

(B) Learn a best practice will take about 4-5 minutes and then you can simply use format strings like "m/d/yyyy"

I will always choose B. But most people? All they want is (A). they don't want to understand, or improve. They just want to cut and paste and "make it work right away!". The problem is that we propagate that here by providing those bad option (A) solutions all the time, thus decreasing the overall understanding of SQL and programming in general and promoting "worst" practices instead of best practices. It's definitely a pet peeve of mine here at SQLTeam, especially when people giving answers should know better.

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

While I agree with you I think it would be unfair not to share how to do something even if it is not the best way. I think it is definitely important to point out that it is not the best way to do it, but it is good for the purpose of learning to know how things can be done in the backend as well.

That's fine, but you should explicitly tell them that what you are showing them is a bad idea, more work than necessary, and not a best practice.

If you are going to help them, why not just show them the best way to do it in the first place?

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-08-31 : 11:31:08

[
If you are going to help them, why not just show them the best way to do it in the first place?
]

Agreed

/*-----------------------
Words are but constrained thoughts.
------------------------*/

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 04:48:37

Can I just butt in to say I agree please?