Date and DateTime format in SQL Select, Insert and Update
Posted on 2006-05-01
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.