Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need to convert and combine date and time on SQL

Posted on 2007-04-10
3
Medium Priority
?
2,145 Views
Last Modified: 2008-01-09
Hello,
I have date and time entered separately into my database table. Date is of the data type datetime so it is in the format of 2007-04-10 00:00:00.000
Time is enetered as a varchar(7) - so it is in the format of: 06:00PM
I am doing conversion of time entries using the following syntax: CONVERT(datetime, sl.bgn_time) that produces: 1900-01-01 18:00:00.000
I need to combine date and time entries into one datetime format so I can compare that to getdate() and select all entries that have datediff(hh,datetime,getdate())<24 - another words -   need to get all entries that have date and time set at les then 24 hours from now.
I need to do it in the stored procedure so it all has to be in T-SQL.
Thanks for your help.
0
Comment
Question by:lescluster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Expert Comment

by:chiragkhabaria
ID: 18882718
datediff(hh,DateAdd(hh,datepart(hh,Convert(DateTime,TimeCol)),DateCol) ,getdate())<24
0
 
LVL 6

Accepted Solution

by:
Gokulm earned 2000 total points
ID: 18882728
Try this to concatenate date and time

SELECT CONVERT(datetime, datefield, 101) + CONVERT(datetime, sl.bgn_time)
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18882885
Does the date field ever have a time component?  If not (or if it is always 00:00:00.000,  you should be able to add them simply as

DateField + TimeField

SELECT *
FROM Table
WHERE (DateField + Timefield) < DateAdd(day, -1, getdate())

If you need to remove the time from the date field:

SELECT *
FROM Table
WHERE (convert(datetime,convert(varchar(11), DateField, 120) + ' ' + TimeField)) < DateAdd(day, -1, getdate())
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question