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 |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2007-01-09 : 12:04:18
|
| I have a null in the date field.How would I display it as a blank?if I use the isnull function, it converts it into 1/1/1900...I need this help quick..Thanks in advance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-09 : 12:29:44
|
| Best way is to format the date in your front-end application.Kristen |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-09 : 12:31:49
|
| If you want SQL Server to do this, you have to pass all dates as strings, rather than dates.SELECT ISNULL(CONVERT(VARCHAR, <YourDateTimeColumnNameHere>, 112), '') AS theDatePeter LarssonHelsingborg, Sweden |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-01-09 : 12:32:28
|
NULL indicates no particular value, unknown. how about you just return the nulls to the client app and if the app encounters one, it shows an empty string?
www.elsasoft.org |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2007-01-09 : 12:34:35
|
| A "blank" isn't a date, it's a string. Sounds like you need to do some data type conversion:DECLARE @ExampleDate DATETIME;SELECT @ExampleDate = GETDATE();SELECT COALESCE(CONVERT(VARCHAR(10),@ExampleDate,101),'');SELECT @ExampleDate = NULL;SELECT COALESCE(CONVERT(VARCHAR(10),@ExampleDate,101),'');I use format 101, which looks like MM/DD/YYYY, so if you want to use something else you'll have to review the CONVERT function in books online. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-09 : 12:39:14
|
| Note that if you DO do the conversion in SQL you are returning a String to the application and NOT a Date, and the application may not handle the data correctly (depending on whether it parses it correctly, and using the same Locale as SQL server etc.).Plus if the application is going to treat the data as a Date you have the extra effort of:SQL Server converts date to stringApplication parses string back to dateIf you are getting SQL Server to export the data to a text file, for example, then getting SQL Server to do the formatting is fine.If the data is going to an application then that would be the better place to handle this issue.Kristen |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-01-09 : 12:40:34
|
| Thank you Peso, I used your idea and it worked.Kristen, Since I do not control the front end app, I have little say in how it is handled.Thanks for the prompt response though. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-09 : 12:56:15
|
| "Since I do not control the front end app, I have little say in how it is handled"Yeah, I and others [by the looks of it] were expecting something like that which is why we described the need to consider the Data type of the object you are passing to the front end.Whilst converting it to a string in SQL Server may fix the problem for now you are more than likely going to "submarine" a bigger problem!Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-09 : 14:29:42
|
quote:
Originally posted by pareshmotiwala
Thank you Peso, I used your idea and it worked.Kristen, Since I do not control the front end app, I have little say in how it is handled.Thanks for the prompt response though.
As Kristen has told you, the front end should handle it fine -- in fact, they should EXPECT Null values when a date is "missing", and not all of the dates converted to varchars! formatting should always, always be done at the front end.- Jeff |
|
|
|
|
|
|
|
|
Null in date field