how to convert smalldatetime to string in sql?

[lydro]

I have a data type is smalldatetime in the table, when I bind to my DataTable in VB.NET, when there is value in this column, the output is fine, but when it's NULL, then it gets error of the data type. In the sql, if it's string, I do ISULL(firstname, '') AS firstName inside sql query, then the output is fine, but I don't know how to convert the null smalldatetime. Is there any idea?

 

[SqueakinSweep]

2 ways to approach this problem. One is either to make sure that you never have a NULL date in the table and instead use a date such as 1/1/1900 to represent a Null or Empty Date.

The second way, and I am presuming you are pulling the data from SQL is to use the ISNULL function to translate the NULL date to a date which is suitably out of range to be identified as such
This ISNULL works in SQL

select isnull(sd,cast('01/01/1900' as datetime)) from dt

Sweep
...if it works dont mess with it

 

[SqueakinSweep]

or as a String, do the following

select isnull(convert(datetime,sd,102),'') as datestring from dt

Sweep
...if it works dont mess with it

 

[lydro]

but there is still a little problem is in the vb.net appl.
I do this.
Dim OrderDate AS Date

if it's null, then the datestring will be string, and in the vb, I dim the OrderDate to Date type, it won't be match. what should I do for this. thanks!

 

[SqueakinSweep]

dim OrderDate as Date
Orderdate = CDate(mydatevar)

where mydatevar is the datevalue back from SQL, usually in a Datareader or DataTable.


Sweep
...if it works dont mess with it

 

[lydro]

how can I convert a Date to String?

dim OrderDate as Date

if mydatevar ISNULL then
OrderDate = "" ***how to convert to string in here**?
else
OrderDate = mydatevar
End if
mydatevar is a datetime type.

thanks!

 

[SqueakinSweep]

If you declare orderdate as a date, then you cant have it as a string or a date. The code I gave you earlier from SQL was to either get the Date as a string or a date. Im guessing you have got it back from SQL as a Date?, or you've ignored it altogether, and its coming back as either a Date or Null

you can use ISDBNULL(myDateVar) to find out if your variable is Null, but you need to be consistent with it, its either a string or a date, but not both dependent on whether or not it is Null


Sweep
...if it works dont mess with it

 

[lydro]

okay..I know it can't be both date and string..so that's I'm trying to fix it out. but I still can't make it works.

this is what I want it to be output, If myDateVar is not Null, then output the Date, if myDateVar is Null, then output nothing(or an empty string)...the problem is I'm getting error of the empty string..

I'm using so many different ways to do it, but it still doesn't work.

I change the OrderDate to string

inside the dataReader:

If Not DR.IsDBNull(17) Then
orderDate = DR.GetDateTime(17).ToString("MM/dd/yyyy")
Else
orderDate = DR.GetDateTime(17).ToString("") ???here is the problem??how can I make it to empty string??
End If

 

[SqueakinSweep]

ok..orderdate is now String

orderDate = ""
If Not DR.IsDBNull(17) Then
    orderDate = DR.GetDateTime(17).ToString("MM/dd/yyyy")
end if

Sweep
...if it works dont mess with it

 

[lydro]

Now it didn't give me any error, but all the orderDate output is empty, even there is value in it. why?

 

[SqueakinSweep]

Id suggest you use the debugger and look at what is going on as you iterate through the datareader.
Put some code in as follows and step through it.

while dr.read
dim s as string = cstr(dr.oSQLDataReader.Item("datefieldname"))
end while

I will also reiterate, that in my opinion it is far simpler to handle NULLS in your SQL Query.



Sweep
...if it works dont mess with it

 

[RiverGuy]

What are you binding it to? A textbox?