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 |
|
wilhan01
Starting Member
4 Posts |
 Posted - 2012-09-17 : 12:09:30
|
| How can I replace the date: "1/1/1900 12:00:00 AM" in my query with a blank field? I currently have to find and replace when exporting to Excel but would like to automate the process.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 13:04:02
|
| you cant unless you change the data type to varchar (text in excel)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
wilhan01
Starting Member
4 Posts |
Posted - 2012-09-17 : 13:21:57
|
| So it is not possible in a DateTime field? There is no way to change to a blank with any logic? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 14:32:44
|
'' is not valid datetime valuepassing a value of '' to datetime causes it to implicitly convert it valid base date value which is '19000101 00:00:00'see below to understand how sql server does implicit conversion for various date datatypesselect cast('' as date),cast('' as datetime),cast('' as datetime2),cast('' as time)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
wilhan01
Starting Member
4 Posts |
Posted - 2012-09-18 : 10:21:19
|
| The " (quotes) are not part of the field, it should just be: 1/1/1900 12:00:00 AM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-18 : 10:56:43
|
quote:
Originally posted by wilhan01
The " (quotes) are not part of the field, it should just be: 1/1/1900 12:00:00 AM
why not cast it to varchar before export if you want to retain blank values? keep in mind that this will cause issues if you try to do some date manipulation with converted data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
wilhan01
Starting Member
4 Posts |
Posted - 2012-09-18 : 11:02:39
|
| There will be other dates that I want in the DateTime format when exporting so I can't use VarChar. I was thinking there might be a way to search for the retrieved value of 1/1/1900 12:00:00 AM and replace with a blank. |
|
|
|
|
|
|
Replace Certain Dates with Blanks