| Author |
Topic |
|
werhardt
Constraint Violating Yak Guru
270 Posts |
 Posted - 2008-07-25 : 09:17:34
|
| Right now I have a date of 2008-01-01, I want it to say 01/01/2008 how can I convert that? This is a datetime, null field |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-25 : 09:19:51
|
do it it front end or in T-SQL use convert()
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-25 : 09:20:45
|
quote:
Originally posted by werhardt
Right now I have a date of 2008-01-01, I want it to say 01/01/2008 how can I convert that? This is a datetime, null field
Where do you want to show the converted dates?MadhivananFailing to plan is Planning to fail |
|
|
|
VGuyz
Posting Yak Master
121 Posts |
Posted - 2008-07-25 : 09:20:59
|
| Check thisSELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] |
|
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2008-07-25 : 09:50:36
|
This field has dates already in the report coming out as 2008-01-01 I just need my report to come out with saying 01/01/2008. Can I do somekind of replace?replace(pro_eff,'/','')...something.quote:
Originally posted by VGuyz
Check thisSELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-25 : 09:54:17
|
datetime is not stored in the database in any particular format but in an internal representation. what you see as 2008-01-01 is just how Query Analyzer or SSMS present the datetime to you.You should be formatting the datetime value in to the format you want in your front end application. If you have to do it in SQL, then use the convert() function. Please check out the Books Online for more details on convert()
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
VGuyz
Posting Yak Master
121 Posts |
Posted - 2008-07-25 : 10:15:39
|
| HI,Check with this,select convert(varchar(15),convert(datetime ,('2008-01-01')),101) |
|
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2008-07-25 : 11:34:34
|
Thanks for your help.quote:
Originally posted by VGuyz
HI,Check with this,select convert(varchar(15),convert(datetime ,('2008-01-01')),101)
|
|
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2008-08-11 : 11:16:54
|
| I have a field that is a datetime field, but I needed the format to look like 01/01/2008. The formula that I had worked, see below[Termination Date] =Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_trm), 101) End,when I wanted to use it as a date range it won't read it as a date.How can I fix this? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-11 : 11:33:33
|
quote:
Originally posted by werhardt
I have a field that is a datetime field, but I needed the format to look like 01/01/2008. The formula that I had worked, see below[Termination Date] =Case CONVERT(VARCHAR(10), (dgh.prop_trm), 101)WHEN '03/23/1977' THEN ''else CONVERT(VARCHAR(10), (dgh.prop_trm), 101) End,when I wanted to use it as a date range it won't read it as a date.How can I fix this?
why are converting it to varchar in first place? whats the significance of 23/03/1977? is it your default date value? |
|
|
|
werhardt
Constraint Violating Yak Guru
270 Posts |
Posted - 2008-08-11 : 11:57:34
|
| my boss wants it to have a better format. The 1977 means that here is nothing in that field. It's the way our one systm works. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-11 : 12:00:15
|
quote:
Originally posted by werhardt
my boss wants it to have a better format. The 1977 means that here is nothing in that field. It's the way our one systm works.
The formatting is a presentation issue which needs to be dealt with at your presentation layer which is your front end application. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-11 : 12:01:24
|
[code]SELECT CONVERT(VARCHAR(10), NULLIF(dgh.prop_trm, '03/23/1977'), 101) AS [Termination Date]FROM YourTable[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Convert date