Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Concat date part to time part

Posted on 2011-02-28
6
Medium Priority
?
1,036 Views
Last Modified: 2012-05-11
This is SQL 2000....

I have a piece of SQL code and I do a comparision. See below.
That works but I created a table to hold "cut off" hours for shipments. Now, I dont know how to do the comparison..i somehow need to concat "date" of "getdate" to "time" from the table...then do the comparison..

see below please
--This is how it currently works...we want to check the *hour*
DECLARE @CUTOFF DATETIME
declare @poDate datetime

SET @CUTOFF = dateadd(hour,16,CONVERT(VARCHAR,GETDATE(),101))
set @poDate ='2011-02-04 10:38:00.000'

-- Then compare
If @poDate <= @cutoff Begin
 .....
End

------ This is what i want to do -----------

-- But for cutoff date, I created a table with values like this, looking at the *hour*
 --1900-01-01 15:00:00.000
 --1900-01-01 19:00:00.000
create table #cutoff
(
   id int identity,
   shipmethod varchar(20),
   cutoffHour datetime

)
insert into #cutoff ('USPS','1900-01-01 16:00:00.000')
insert into #cutoff ('UPSA','1900-01-01 19:00:00.000')
/*
But now, the date is "1900-01-01" and I cant just do a comparision. I think I need to somehow get the "date" part from getdate()
and concat it to Time part and then do the comparision. 
*/
select @cutoffhour = cutoffhour from #cutoff --somehow concat Date of getdate to time from cutoff hour

If @poDate <= @cutoffHour Begin
 .....
End

Open in new window

0
Comment
Question by:Camillia
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 35002974
create table #cutoff
(
   id int identity,
   shipmethod varchar(20),
   cutoffHour datetime

)
insert into #cutoff  select 'USPS',CONVERT(varchar(10) , GETDATE(), 120)+' 16:00:00.000'
insert into #cutoff  select 'UPSA',CONVERT(varchar(10) , GETDATE(), 120)+' 19:00:00.000'

select * from #cutoff
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 35003937
pls check below code. It will  concat "date" of "getdate" to "time" from the table:

select cast(getdate() as int)+ @cutoffhour
0
 
LVL 6

Assisted Solution

by:anushahanna
anushahanna earned 500 total points
ID: 35003945
the below are other date formats you can put to use:
http://www.sql-server-helper.com/tips/date-formats.aspx
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 500 total points
ID: 35004470
IF YOU HAVE A CUT OFF HOUR THEN WHY NOT JUST STORE The hour and compare the hours?


isn't your table too simplistic? won't the cut off time depend on the actual date/day of week etc....

so you should have two tables... 1 for the default  cut off hours per day of the week... by method
and the second as an exception table to cater for national holidays etc....

hour(date)

convert(char(8),getdate(),112)  give the YYYYMMDD date to which you can then attach a character time component

 as HH:MM  (with a space between the date and the time component....
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 500 total points
ID: 35007117
Hi,

In your original query you can use the below line:
 
select @cutoffhour = convert(char(10), @poDate, 111) 
              + cutoffhour from #cutoff --somehow concat Date of getdate to time from cutoff hour

Open in new window


And this will include your cutOffHour with the PoDate.
0
 
LVL 7

Author Comment

by:Camillia
ID: 35007430
going to try now. will post back
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how the fundamental information of how to create a table.

877 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