TSFLLC
asked on
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
If I use only "ISNULL(violation.reported
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.
Phil
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
If I use only "ISNULL(violation.reported
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.
Phil
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.
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well.....
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.
Thanks!
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.
Thanks!
good luck with your project!