Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting datetime

Author  Topic 

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-06-22 : 10:05:29
I have a datetime field to store the time. My user wants to select the time in 15 minute intervals from a dropdownlist. So, I bind my (web) dropdownlist to this sp which displays it as 12:00, 12:15 etc etc

SELECT *, LEFT(right(CONVERT(VARCHAR(30),TI_Time,108),8),5) as "ShortTime" from tbl_Time

My question is, when I save the selected time back to the sql table how do I convert 12:00 back to 1900-01-01 12:00:00.000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-22 : 10:20:23
Just convert it to datetime:

select cast('12:15' as datetime)

Here's a shorter query that does the same as what you posted:

select *, CONVERT(char(5), TI_Time, 8) [ShortTime] from tbl_Time

Go to Top of Page

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-06-22 : 11:01:24
Thanks for that but do I convert it in my stored procedure which updates my table or in my asp.net code ?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-22 : 11:27:44
Probably easier to do in the stored procedure.
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-23 : 00:39:12
>> My question is, when I save the selected time back to the sql table how do I convert 12:00 back to 1900-01-01 12:00:00.000 <<

Did you know that we now have a TIME data type, so you don't have to this silly, expensive coding any more?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page
   

- Advertisement -