Solved

Need to convert and combine date and time on SQL

Posted on 2007-04-10
3
2,132 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 500 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

737 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