Convert DateTime To Date Using Rounding UDF

Feb 25, 2006

This UDF will strip off the time portion of a DateTime. It can give you either midnight last night, or midnight tonight.

Lets say you have two datetime values @dateStart and @dateEnd, and you want to select records between these dates (excluding any time portion), then you would do:

SELECT *
FROM MyTable
WHERE MyDateTimeColumn >= dbo.kk_fn_UTIL_DateRound(@dateStart, 0)
AND MyDateTimeColumn < dbo.kk_fn_UTIL_DateRound(@dateEnd, 1)

If you want to display dates, without the time, then do:

SELECT dbo.kk_fn_UTIL_DateRound(MyDateColumn, 0) AS [My Date]
FROM MyTable


--
PRINT 'Create function kk_fn_UTIL_DateRound'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_DateRound]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.kk_fn_UTIL_DateRound
GO

CREATE FUNCTION dbo.kk_fn_UTIL_DateRound
(
@dtDatedatetime,-- Date Value to adjust
@intRoundint-- 0=Round down [Midnight last night], 1=Round up [Midnight tonight]
)
RETURNS datetime
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_DateRoundConvert date to midnight tonight
*For a "limit" date of '01-Jan-2000' the test needs to be
*MyColumn < '02-Jan-2000'
*to catch any item with a time during 1st Jan
*
*SELECTdbo.kk_fn_UTIL_DateRound(GetDate(), 0)-- Midnight last night
*SELECTdbo.kk_fn_UTIL_DateRound(GetDate(), 1)-- Midnight tonight
*
* Returns:
*
* datetime
*
* HISTORY:
*
* 28-Jul-2005 KBM Started
*/
BEGIN

SELECT@dtDate = DATEADD(Day, DATEDIFF(Day, 0, @dtDate)+@intRound, 0)

RETURN @dtDate
/** TEST RIG

SELECT'01-Jan-2000', dbo.kk_fn_UTIL_DateRound('01-Jan-2000', 0)
SELECT'01-Jan-2000', dbo.kk_fn_UTIL_DateRound('01-Jan-2000', 1)

SELECT'01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', 0)
SELECT'01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', 1)

SELECT'28-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('28-Feb-2000 01:02:03', 0)
SELECT'28-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('28-Feb-2000 01:02:03', 1)

SELECT'29-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('29-Feb-2000 01:02:03', 0)
SELECT'29-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('29-Feb-2000 01:02:03', 1)

**/
--==================== kk_fn_UTIL_DateRound ====================--
END
GO
PRINT 'Create function kk_fn_UTIL_DateRound DONE'
GO
--

Kristen

View 4 Replies


ADVERTISEMENT

How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date

Sep 21, 2006

HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance

View 3 Replies View Related

Convert Datetime String To Datetime Date Type

Mar 11, 2014

I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the

My date and time string from the application looks like : 3/11/2014 12:57:57 PM

View 1 Replies View Related

Convert Date-string To Datetime

Jan 6, 2015

I am trying to convert a date-string of this format (DDMMMYY - ex. 06JAN15) to datetime but I keep on getting errors:

codes that I tried:
convert(datetime, '06JAN15', 6)
convert(datetime, '06JAN15', 112)

What date format code should I use for DDMMMYY?

View 13 Replies View Related

How To Convert Datetime To DATE '1936-04-13 07:00:00.000'

Jul 23, 2005

Does anybody can help me to convert datetime value let's '1936-04-1307:00:00.000' to DATE as '04/13/1936'. I need this value in the Excell asa DATE format.Thanks in advance

View 2 Replies View Related

Convert Numeric Date To DateTime

Apr 29, 2008

Hello,
I have a column called 'FileDate' that is NUMERIC (18,0). The format is YMMDD. A sample of March 1st and 2nd, 2008:

80301
80302

Is there a way to convert/cast this to DateTime or SmallDateTime?

Thank you for your help!

cdun2

View 5 Replies View Related

SQL 2012 :: Convert Datetime Into Date In SSIS

Dec 7, 2014

I have define two variable of Datetime type @Sdate and @Edate.

1. @Sdate = DATEADD ("DD", -5, GETDATE())
2. @Edate = GETDATE()
3. Using Forloopcontainer for pulling the data into batches
( @sdate = dateadd ( "HH" , 1, @sdate))

Now since i am using getdate() to define @Sdate. my variable gets data as ( 2014/12/08 11:43:00AM)

Converting GETDATE( 2014/12/08 11:43:00AM) to only date 2014/12/08 00:00:00AM.

I tired using DT_WSTR which works fine (converting datetime int String).

Problem occurs when i am going through For Loop container . Since i have used assignment of @Sdate is says its not allowed.

View 9 Replies View Related

Convert A Date Stored As A String Into A Datetime

May 28, 2007

Hello forum,
Is it possible to convert a date stored as a string into a datetime with integration services 2005? My attempts with the €œdata conversion€? fail. The string type form of the date is €˜yyyy-mm-dd€™ and the desired result for use in a Union All is €˜dd/mm/yyyy 12:00:00AM.€™ This outcome is needs so that match on the date can populate a fact table, as the results are coming from two different databases.
All advice/help welcomed.
Ian

View 4 Replies View Related

Convert Datetime Field To Display Date

Aug 9, 2006

I have a field that I would like to only display the date as mm/dd/yyyy. Current field shows mm/dd/yyyy hh:mm:ss AM.

View 10 Replies View Related

T-SQL (SS2K8) :: Convert Datetime To W3C Date And Time Format

Dec 21, 2014

I have a standard datetime and I need to convert it to the client specification of:

YYYY-MM-DDThh:mm:ssTZD
eg: 2009-04-16T19:20:30+08:00

I am not sure of the easiest way to do this.

The test code below gets me part of the way but I am unsure on how to get the offset on the end without hardcoding to much.

DECLARE @datetime DATETIME = '2014-12-20 12:30:00'
SELECT CONVERT(VARCHAR(30),@datetime,127)

View 2 Replies View Related

T-SQL (SS2K8) :: How To Concatenate / Convert Date And Time Into Datetime

Aug 3, 2015

We have 2 columns:

StartDate - data type Date
StartTime - data type Time

I need to combine them into a DateTime data type. For now, I convert each of them into varchar, insert space in between, and convert to DateTime, like this:

convert(datetime, convert(varchar(10), EndDate , 101) + ' ' + CONVERT(varchar(20), EndTime,24))

Is there a better, more elegant way to do this?

View 9 Replies View Related

Cannot Convert Yymmdd Unicode Date String To SQL Server DateTime

Jan 28, 2008



Hello,
I have tried a few different things within a Derived Column transform to convert a unicode yymmdd date string to SQL Server DateTime.

Does anyone have ideas on how to do this?

Thank you for your help!

cdun2

View 4 Replies View Related

Rounding / Convert To Percent

Sep 26, 2013

I have a question about rounding and converting to percentage and adding in the '%'..This is my original code

SELECT * FROM
( SELECT Baby,
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) as BabyValue
FROM NewBorn
WHERE Category = 'Boy'

[code]....

But I also need my data to have the '%' and it should have 2 decimal place which is as below, I have tried to make it this way

CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 + '%' as BabyValue

But it prompt me the error 'Error converting data type varchar to numeric.'

A B C D
0.22%0.29%0.11%0.32%

View 2 Replies View Related

Convert Decimal To String Is Without Rounding Up

Jul 27, 2004

Hello I'm trying to write a SQL Statement along the lines of....

SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue

Where stringField is a String field and decimalField is a Decimal Field in my Table.
In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.

How can I get it to keep the Decimal Places?

View 5 Replies View Related

DB2 To SQL Datetime Conversion (Rounding) Problems

Mar 11, 2008



I converting DB2 date thru SQL server linked server. The datetime information is select from the DB2 correctly, but when I convert it to a datetime via a SQL function that I wrote, the milliseconds on the converted date are incorrect. The last digiti arbitrarily rounds up/down or does not round at all. I select the dates as a varchar from DB2 and string manipulate the Date field to correctly format the data, perform the convert function and presto the date is incorrect. Here is a code snippet I run on Query analyzer to get the resulse in question.



declare @datestring varchar(50)

declare @date varchar(10)

declare @time varchar(12)

declare @check as varchar(4)

declare @hold as varchar(4)

declare @int as integer

set @datestring = '1995-11-10-09.34.29.465123'

if len(@datestring) > 10

begin

set @datestring = substring(@datestring,1,len(@datestring)-3)

set @date = substring(@datestring,1,10)

set @time = replace(substring(@datestring,12,12),'.',':')



select @datestring, @date, @time,convert(datetime,@date + ' ' + @time)

end

else

select convert(datetime,@datestring)


Resulsts are as follows:
1995-11-10-09.34.29.465 1995-11-10 09:34:29:465 1995-11-10 09:34:29.467




Anyone have any idea what is going happening or any suggestions to correct this?

View 5 Replies View Related

Rounding Consistency When Casting -- Datetime Type

Mar 5, 2008

I would expect this query:



select

case when cast(DT as int) = cast(cast(DT as float) as int)

then 'Consistent'

else 'Inconsistent'

end

from (select cast('27 Jan 2008 13:00' as datetime) as DT) X


to always return 'Consistent'. My expectation is not satisfied; the question is, is it unfounded?

View 13 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

Jul 20, 2015

Working on a new database where the Date and Time are stored in a Date Time Field.

Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:

transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)

How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?

This works well for converting the transDate Part in the select statement:

   dbo.IntegerToDate(at.transDate) as transDate

   * That returns: "2015-07-16 00:00:00.000"

* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!

Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.

View 3 Replies View Related

Date Rounding For A View

Oct 1, 2007

I am trying to incorporate a few columns into a view that each shows a certain value based on a logged datetime specific to the value. Unfortunately, the logtimes are accurate down to milliseconds, and each value has it's own logtime. (They're suppsoed to log at midnight, but sometimes log a few seconds early or late).

I want to be able to round up to 00:00:00 if it's 23:59:59 and down to the same time if it's 00:00:01. I can't very well just drop the time component because if a device logged at 00:00:01 on Aug 4 for the Aug 3rd average, and 23:59:59 for the Aug. 4rd data, then I'd have two Aug 4th values and zero Aug. 3rd values.

Additionally, I need to keep this in a datetime format for reporting purposed in Crystal Reports.

Am I asking too much?

View 18 Replies View Related

Weird Date Rounding

Jul 20, 2005

SQL Server 7.0The following SQL:SELECT TOP 100 PERCENT fldTSRID, fldDateEnteredFROM tblTSRs WITH (NOLOCK)WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00',102))AND(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))returns this record:fldTSRID: 4fldDateEntered: 24/11/2003Hello? How is 24/11/2003 <= '2003-11-23 23:59:59'?I tried decrementing the second predicate by seconds:(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:30', 102)))returns the record, but(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:29', 102)))does NOT.What is happening here?Edward============================TABLE DEFINITION:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRNotes_tblTSRs]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRNotes] DROP CONSTRAINT FK_tblTSRNotes_tblTSRsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRs]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTSRs]GOCREATE TABLE [dbo].[tblTSRs] ([fldTSRID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerID] [int] NOT NULL ,[fldWorkshopGroupID] [int] NULL ,[fldSubjectID] [int] NULL ,[fldReasonID] [int] NULL ,[fldFaultID] [int] NULL ,[fldContactID] [int] NULL ,[fldMileage] [int] NULL ,[fldFirstFailure] [smalldatetime] NULL ,[fldNumberOfFailures] [int] NULL ,[fldTSRPriorityID] [int] NULL ,[fldTSRStatusID] [int] NULL ,[fldAttachedFilePath] [char] (255) NULL ,[fldFileAttached] [smallint] NOT NULL ,[fldFaultDescription] [ntext] NULL ,[fldFaultRectification] [ntext] NULL ,[fldEmergency] [int] NOT NULL ,[fldDateEntered] [smalldatetime] NOT NULL ,[fldEnteredBy] [int] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

View 8 Replies View Related

How To Convert Datetime From Text/char To Datetime

Jul 20, 2005

Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -

View 1 Replies View Related

Convert DateTime To A DateTime With Milliseconds Format

Nov 5, 2007

Hi,

I am trying to access a date column up to millisecond precession. So I cast date to as follows:



Code BlockCONVERT(varchar(23),CREATE_DATE,121)


I get millisecond part as a result of query but it€™s €œ000€?.

When I try to test the format by using getDate instead of DateTime column I get right milliseconds.





CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return

View 4 Replies View Related

How To Convert Datetime From Varchar To Datetime

Sep 11, 2007

hi,
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.

following are the sample data that is theer in the older table for the date.


12/12/2003
1/13/2007
01132004
1-1-2004
1.2.2001



there is no uniformity of the data that is stored currently.



thnkx in adv.
rahul jha

View 11 Replies View Related

How To Convert A Date (date/time) To A Julian Date

Jun 13, 2002

In SQL Server 2000:

How do I convert a Julian date to a Gregorian date?

How do I convert a Gregorian date to Julian?

Examples please.

Many thanks in advance.

Gary Andrews

View 2 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

Sep 28, 2007

Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,

I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.

I have no choice of making date column in SQL to string or Varchar etc,

is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).

many many thanks,

View 2 Replies View Related

How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?

Nov 13, 2007

Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).

I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.

The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?

Please advise. Thanks.

Best.
K7

View 1 Replies View Related

Convert To DateTime

Dec 26, 2006

I have a date filed 12/26/2006 and a time field 7:00am. How can I combine them in my select statement and get a DateTime field.

View 3 Replies View Related

Convert Datetime.

Mar 29, 2004

Hello All,

In following statement in SQL, I'm first converting 'IssueDate' with style 101 into 'nvarchar' then converting to Datatime.

convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')


Which is right in below one.

1. Do I need to first convert into nvarchar then datetime.
e.g. convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')


2. Otherwise can I directly convert into datetime.

convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/29/2004')


Please reply to me asap.


Regards,
M. G.

View 3 Replies View Related

Convert Datetime To MM/DD/YY HH:MM AM/PM

Apr 7, 2008

Hi,
Is there a way to convert the date time column to MM/DD/YY HH:MM AM/PM format.
I tried
Select Convert(varchar , Getdate(),100). But this is not in MM-DD-YY format..

Thanks in advance..

View 4 Replies View Related

Convert Datetime.

Jul 11, 2006

Hi all..its kinda hard for me hw to figure out this, hopefully any of u guys can help me out with this super simple problem..

here is my query..
select convert(char(50),dateadd(day,-7,getdate()),105)
its because i want it to look last week data. BUT i get the format like this '03-07-2006'

what i want is it to be like this '20060703' how do i do that?

Thanks,
Jack

View 3 Replies View Related

Convert Datetime

Aug 29, 2007

I'm having trouble converting a date of birth field, datatype int from yyyymmdd to mmddyyyy.

select 'DOB' = Convert(char(10), pat_dob, 101) from patfile

what am I missing?

View 4 Replies View Related

Convert Int To Datetime

Dec 14, 2007

I have a table with an int field that I'm trying to
insert into a datetime field, however, there are 0's in the int field. How do I write a case statement to change the 0's to '01/01/1900' and then store the datetime field as 'mm/dd/yyyy'? The data is currently coming in as yyyymmdd as int.

Sample Data:
19720518
19720523
19720523
19720601
19720603
19720609

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved