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.
| Author |
Topic |
|
kaplooeymom
Starting Member
5 Posts |
 Posted - 2006-06-08 : 17:26:03
|
| Data is entered normally in smalldatetime columns.I want dates returned as "2006.06.08" (yyyy.MM.dd, two digits for month and day always)I've created a UDF to convert the date parts to strings (copied code) and put the periods in. However, I can't figure out how to add the padded zero.In Vb, there's a format function, but I can't find anything similar in SQL Server 2000. Thanks in advance for any answers! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-08 : 17:40:15
|
| It would be best to do your date formatting in your application rather than in T-SQL. But to do it in T-SQL, you can use CONVERT with a style. Check out CONVERT in SQL Server Books Online for details.Here is an example using the format that you want:SELECT CONVERT(varchar(10), GETDATE(), 102)But do it in the presentation layer and not in your stored procedure!Tara Kizeraka tduggan |
 |
|
|
kaplooeymom
Starting Member
5 Posts |
Posted - 2006-06-11 : 09:50:26
|
| There's a reason I don't want to do it in the presentation/application layer. All dates in this app are formated this way, and I'm trying to ease the load, and create a udf to do this for all the sprocs returning viewable data.I looked at BOL, and it wasn't real helpful with CONVERT, but I'll look for more examples. Updated - the sample was perfect (although why the next guy didn't cut me a break for replying on a non-workday, without access to server, I don't know). Thanks for the help! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-11 : 10:11:05
|
quote:
Originally posted by kaplooeymom
There's a reason I don't want to do it in the presentation/application layer. All dates in this app are formated this way, and I'm trying to ease the load, and create a udf to do this for all the sprocs returning viewable data.I looked at BOL, and it wasn't real helpful with CONVERT, but I'll look for more examples. Thanks for the help!
More examples?Tara gave you exactly the code you wanted. Did you even try it?select DT = convert(varchar(10), getdate()-5, 102)DT ---------- 2006.06.06(1 row(s) affected) CODO ERGO SUM |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-06-12 : 20:53:17
|
quote:
Originally posted by kaplooeymom...Updated - the sample was perfect (although why the next guy didn't cut me a break for replying on a non-workday, without access to server, I don't know)...
I did cut you a break. I demonstrated the code to show you that you already had the answer in front of you, since it was obvious that you had replied without even trying it.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Specific Date format in sproc?