BradleyCleveland
asked on
How to store and display ONLY time value of datetime field
I can connect to sql server from Microsoft Access. If I put a time value in a datetime field, it displays in the table correctly. eg. 4:00:00 PM
If I try to input a record using sql stored procedures, it always stores and displays the date 1/1/1900 in front of the time even though I send the time to the table like this: CONVERT(char(10), Login, 8). How can I write a stored procedure that will store and display ONLY the Time value. It's obvious that the table can store it that way because it works when sending it from Access.
If I try to input a record using sql stored procedures, it always stores and displays the date 1/1/1900 in front of the time even though I send the time to the table like this: CONVERT(char(10), Login, 8). How can I write a stored procedure that will store and display ONLY the Time value. It's obvious that the table can store it that way because it works when sending it from Access.
joevi
select convert(varchar(10),login,
The database always stores the Date + Time. If you want to store ONLY the Time, you'll have to do that in a separate field set to Double or something of that nature.
Viewing ONLY the time is UI consideration; in other words, you'd typically format the data the way you wish to view it when you fill your form.
Viewing ONLY the time is UI consideration; in other words, you'd typically format the data the way you wish to view it when you fill your form.
Hi
What SQL server version are you using?
What SQL server version are you using?
Time field don't exists in SQL 2000 and SQL 2005. In SQL 2008 you can have a variable or column of type "Time".
The MS SQL base date is :1900-01-01
So if you store time in a datetime column you will see the time on the base date.
You can used integer to store minute or hours or Second --> sometimes it's good (like execution duration)
You can also used string to store the "05:00" --> worst way
, but I will prefers working whit datetime and have all my time record on 1900-01-01, Except if I can find a way to used the real date.
The MS SQL base date is :1900-01-01
So if you store time in a datetime column you will see the time on the base date.
You can used integer to store minute or hours or Second --> sometimes it's good (like execution duration)
You can also used string to store the "05:00" --> worst way
, but I will prefers working whit datetime and have all my time record on 1900-01-01, Except if I can find a way to used the real date.
You can't change the way it stores, except by changing to a non datetime format.
You can indirectly change the way it displays.
Create a separate, computed column that displays the time exactly as you want it, then use that column in your display queries.
ALTER TABLE table1
ADD time AS CAST(REPLACE(RIGHT(CONVERT
If you don't want a leading zero in times before 10AM/10PM, change the REPLACE to an LTRIM.
If you want AM/PM in lower case, add a LOWER() around the expression.
You can indirectly change the way it displays.
Create a separate, computed column that displays the time exactly as you want it, then use that column in your display queries.
ALTER TABLE table1
ADD time AS CAST(REPLACE(RIGHT(CONVERT
If you don't want a leading zero in times before 10AM/10PM, change the REPLACE to an LTRIM.
If you want AM/PM in lower case, add a LOWER() around the expression.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.