| Author |
Topic |
|
Jonny1409
Posting Yak Master
133 Posts |
 Posted - 2007-10-08 : 04:48:45
|
Hello,I have a Char(10) field in my table that I am bringing date values into from another table where the format of the field there is datetime.The date format in the current table is "MM/dd/yyyy", but I would like it to be the UK format of "dd/MM/yyyy".I believe I would need to use the CONVERT function, so have used the following :convert(char(10), StartDate) as StartDate but this doesn't seem to work. How can I do this please ?Thanks in advance |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-08 : 04:57:54
|
Again, why do you bother how the datetimes are stored in the database?Formatting is a matter for end-user application.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-08 : 04:59:13
|
| Unfortunately in this instance I'm simply bringing in data from an old system so need it to be in the correct format for the new application.All new dates from now on will be in the correct format. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-08 : 05:02:20
|
In that case, I would create a VIEW containing the data you want, formatted the way you want.If not satisfied with this, try to put an option 103 when converting.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-08 : 05:06:58
|
I've tried putting the 103 :convert(char(10), StartDate, 103) as HolidayDate, but that doesn't seem to work.The value in the old table is :12/27/2007 and I would like it to come across to the new table as :27/12/2007 However, in Query Analyser it shows still in the old format:I've even tried putting this into a view as you suggested, but still it shows as :12/27/2007 |
|
|
|
b4n4n4
Starting Member
2 Posts |
Posted - 2007-10-08 : 05:23:29
|
| Query Analyser: Tools/Options ~ Connections TabCheck 'Use regional settings when displaying currency, date...' |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-08 : 05:27:20
|
This is STILL formatting...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-08 : 06:02:02
|
| Cant you format at front end when you display dates?MadhivananFailing to plan is Planning to fail |
|
|
|
b4n4n4
Starting Member
2 Posts |
Posted - 2007-10-08 : 06:02:54
|
| I agree with Peso, I don't think you really have a problem.If you have a datetime data type and you've managed to get it into the database into a datetime column and it shows as:27/12/2007 or 12/27/2007then it's in the database in the correct format, you can't put 27 in the month as it won't be permitted. The problem seems to be how you want to see it when you run a query over the data.to check just use datepart(dd,yourdate) and it should return 27, and datepart(mm,yourdate) should return 12If this is so it's being held correctly internally and the problem is how you want to show it externally. |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-08 : 06:18:34
|
| But I haven't got it into the DB in a datetime column, I've got it from a DATETIME column and brought it into a CHAR(10) column.And as things go, I need this CHAR(10) column to show "27/12/2007" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-08 : 06:38:37
|
| <<And as things go, I need this CHAR(10) column to show "27/12/2007">>Where do you want to show?MadhivananFailing to plan is Planning to fail |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-08 : 06:56:58
|
| I need this to show as "27/12/2007" in the actual table (i.e. when I look at the table in Enterprise Manager)Currently when I open the table it shows with a field type of CHAR(10) (which is correct) and with a value of "12/27/2007". |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-08 : 07:01:07
|
UPDATE Table1SET Col1 = CONVERT(CHAR(10), CAST(Col1 AS DATETIME), 103)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 08:12:17
|
As Peso has pointed you can't convert a STRING to a specific DATE format, it needs to be in a Datetime format first. So Peso is converting your String to Date and then back to String.Which is why the others have been pointing out that you should be storing it in a Datetime datatype ... avoids all this in-exactitude! and a whole raft of other problems that you may well shortly be experiencing  Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-10-08 : 08:22:01
|
| Jonny - is the datatype in your old system datetime and you have created a column char(10) in your new system? Just about everyone on this forum will tell you that your new design is worse than the legacy design. How it looks when viewing the data in enterprise manager should be utterly irrelevant. EM should not be your tool of choice for viewing data in the first place.Have a read of this - see if you change your mind:http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspxEDITS - typos |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-08 : 09:55:25
|
| Hi Peso,when I try this in Query Analyser it gives me the following error message :Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 10:04:16
|
| SELECT CONVERT(CHAR(10), CONVERT(DATETIME, '12/27/2007', 101), 103)Kristen |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-08 : 10:41:47
|
| Thanks Kristen - that's excellent - it worked a treat.I appreciate all of the help you have all given me, and I'm going to look at changing the field type to a datetime in the near future, but I don't want to do it now as I don't know why it was done this way in the first place (I'm just trying to fix problems created by someone else) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-08 : 10:50:59
|
If you change the formatting for the existing records to fit the new application, what happens when old application inserts more records?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
CONVERT to UK Format