Date and DateTime format in SQL Select, Insert and Update

I'm sure this will be elementary for most of you using SQL.

I'm somewhat at a standstill as to how to format several datet and time fields in my app as associated with SQL statements.  I am in the process
of creating some stored procedures that have both date and time fields in them which may contain data and also may be null.  Several fields that are
blank or null will be included in an ascending display in a grid.

Needless to say I don't want '(null)' to display in my grid, yet when I use the following CONVERT statement I think I lose my ability to ORDER the grid
in date order because the SQL statement is looking at reported_date below as string.

"ISNULL(CONVERT(VARCHAR(10), violation.reported_date, 1),'') AS reported_date, "

If I use only "ISNULL(violation.reported_date,'') AS reported_date"         IT DISPLAYS 1/1/1900

The SQL Server 2000 Bible I am using must be for advanced SQL developers.  It doesn't cover basics like the one above.

Could someone please help me with the following:

1)  Modifying my SQL statement as discussed above.

2)  The best field type to save a group of time values in a table to include in a combo box.

3)  Using the combo box in #2...the best way to format in the combo box and then store a 'time' value in a separate table.  An example would be the Calendar/Appointment Start/End Time fields in Outlook.

Thanks for the help.

Who is Participating?
MageDribbleConnect With a Mentor Commented:
you can alter the column of the datagrid.  set the NullText property = "" and it'll show blanks instead of <NULL>.
Do you want to alter the data in the SQL statement or in your VB application?  VB has a great function called Format() so you type in Format(Now(), "mm/dd/yy") will return 05/01/06.  Or you can change it in the SQL statement but that would be a better question for the SQL Server boards.
TSFLLCAuthor Commented:
The date and time fields would be saved into a datatable and then use parameters/SQL stored procedure for updating.

I have used Format() in MS Access for years in terms of formatting text boxes, SQL statements used for populating grids, and saving into tables.  SQL 2000 apparently doesn't recognize Format().

My problem #1 may be better served in the SQL Server board.  I just included it in here because I have seen a multitude of problems resolved that included SQL as part of them.  My SQL problem really only deals with one date field being displayed in a VB datagrid.

An Access grid doesn't display (null) in a date field.  It just doesn't display anything.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I used to work in Access alot before I moved to SQL Server.  I feel your pain :)

What I did was write a custom UDF that takes a date and returns a string.  I called it FormatDate() and I never know the difference :)

you can go down that road and try it.  you may be able to alter the dataset before it gets binded to the datagrid in VB.NET with the VB format function but I've haven't done much work with that.
TSFLLCAuthor Commented:

I'm a little embarassed now.  Didn't know that using .NullText = "" was going to be that easy.

That completely resolved my #1 problem.  I dont' have to do any type of conversion or adjustment to a date field in a SQL statement with this property being available.  I'm definitely going to re-review all of the properties for a datagrid.

In the meantime, I decided to create my 'time' field as a datetime and enter just valid time format and will work on this by myself for a time.


good luck with your project!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.