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 |
|
gunapriyan
Starting Member
2 Posts |
 Posted - 2010-10-18 : 00:47:12
|
| Dear Sir,In my DB, there is 5000 date values are stored like "DD-MM-YYYY" Format in nvarchar datatype. For Example 18-10-2010And when i convert into date format for the following purpose."Select * from tbl_master order by Convert(datetime,date_of_Appln,103)"Its not working.Can you help me to convert? or Can you help me to replace the "-" symbol to "/"?Thanks in advance.Regards,Guna |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-18 : 01:12:54
|
| That query is only going to set the date format in the order by.When you say the dates are stored in a particular format, do you mean that you're storing dates in a varchar column? Datetime columns have no format, they're stored just as numbers.--Gail ShawSQL Server MVP |
 |
|
|
gunapriyan
Starting Member
2 Posts |
Posted - 2010-10-18 : 01:31:50
|
| Dear GilaMonster,Yes i am storing all the dates in Nvarchar format.Now my client wants a report by datewise. So I am trying. Error...Thanks in advance..Regards,Guna |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-10-18 : 02:18:53
|
| Why are you doing it in the query?Cant you do it in the report?If you are using ssrs then set a expression for the date value & if using Crystal reports you can set the formula field to format it.PBUH |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-10-18 : 03:01:23
|
UPDATE tbl_master SET date_of_Appln = REPLACE(date_of_Appln, '-', '/')
N 56°04'39.26"E 12°55'05.63" |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2010-10-18 : 04:43:03
|
| Why are you storing them as nvarchar instead of one of the dedicated date data types? Having them in a date data type means it is much easier to format when displaying data, date functions can be applied, proper indexes can be build and most importantly entries that are not valid dates are not allowed. |
|
|
|
|
|
|
|
|
Convert Date (dd-MM-YYYY) to (dd/MM/YYYY)