| Author |
Topic |
|
icw
Constraint Violating Yak Guru
378 Posts |
 Posted - 2006-03-09 : 08:26:04
|
| Hi , I am converting a datetime field to a string. The column is called DateScanDate.This is my query;SELECT CAST(DATEPART(Year, DateScanDate) AS VARCHAR(4)) + CAST(DATEPART(Month, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Day, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Hour, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Minute, DateScanDate) AS VARCHAR(2))FROM HAAneurysmScanI would like the month of March to be '03' instead of '3' and the 9th day of the month to be '09' instead of '9'How can I do this?regardsICW |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-09 : 08:34:09
|
| Do not do this in T-SQL, do it at your presentation layer. T-SQL is not for presenting and formatting data, it is for returning raw data. Your presentation layer (report writer, client app, web page, etc) will be able to very easily format your dates any way you want. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-09 : 08:40:43
|
You should perform the formating in your front end application.Anyway, here it isselect replace(replace(replace(convert(varchar(16), getdate(), 121), '-', ''), ':', ''), ' ', '') ----------------------------------'KH' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-09 : 10:11:52
|
| Where do you want to show the formatted dates?Do this in Presentation layerMadhivananFailing to plan is Planning to fail |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2006-03-11 : 01:23:45
|
| I was planning to use this in a trigger that is updating a second table with a 'homemade' unique identifier based on data in the first table. And I wanted the value to always be the same length.RegardsICW |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-11 : 08:45:07
|
quote:
Originally posted by icw
I was planning to use this in a trigger that is updating a second table with a 'homemade' unique identifier based on data in the first table. And I wanted the value to always be the same length.RegardsICW
No offense, but that's a really bad idea. |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2006-03-11 : 12:36:00
|
| None taken, but why? |
|
|
|
Tahsin
Starting Member
34 Posts |
Posted - 2006-03-13 : 09:56:21
|
| Performance reasons, which is why others have suggested that you do this within your application. Doing this at the database level causes it to perform worse ... it would be simpler to do this at the application level since all your processing will be done on the local machine instead of the server. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-03-13 : 10:48:36
|
| A "homemade unique identifier" sounds like trouble, escecially when you are trying to apply formatting to it. If you need a datetime to be part of the primary key of a table, keep it as a date time and use a composite primary key (more than 1 column in the key).Don't convert datetimes to a varchar() and/or concatenate it with other data to build keys! Keep data with the proper datatypes in their own columns.And, if you just need a meaningless key (i.e., some transaction tables might need this), use an IDENTITY, that's what it's there for. |
|
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2006-03-13 : 15:03:34
|
| Thanks all for the heads up |
|
|
|
|
DATEPART