Solved

Need to convert and combine date and time on SQL

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

740 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