Solved

Need to convert and combine date and time on SQL

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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