DateTime Function

Mar 9, 2004

Dear all,

I am writing SQL statement to manipulate datetime value.

Say to add 1 day, 1 minute & 1 hour to datetime '2004-03-10 14:00:00.000'.

I use the following SQL statement;

fac_date_cnt -> integer value 1

book_date -> datetime value '2004-03-10 14:00:00.000'.

UPDATE SCH_SET

SET fac_send_date = DateAdd(day,fac_date_cnt, f.book_date) + DateAdd(hour,fac_hour, f.book_date) + DateAdd(minute,fac_minute, f.book_date)

FROM SCH_SET t, FAC_BOOK f

WHERE upper(set_id) = 'A000001' and upper(pcode) = 'A';

And then the result is very strange:

'2108-06-20 04:00:00.000'

What wrong with this statement?? Should I not use '+' between DateAdd()?? And then what should I do?

Thanks you for suggestion!!!

View 3 Replies

SQL Function Convert INT To DATETIME

May 9, 2007

I have SQL table with dateTime field which is INT type (This field contains a number representing DATE / TIME)
I would like to convert this umber to actuall date time output. However, SQL gives invalid date times. See below)
Appears the resulting date time are all the same.
                    SQL COMMAND
                                Select TimeStamp, cast(convert(TimeStamp,103) As datetime) from winsData2
                    OUTPUT
                                 TimeStamp    Converted TimeStamp
                                2147483647 1900-01-01 00:00:00.343                                 2147483647 1900-01-01 00:00:00.343                                 1178694066 1900-01-01 00:00:00.343                                 2147483647 1900-01-01 00:00:00.343                                 1178688211 1900-01-01 00:00:00.343                                 1178828143 1900-01-01 00:00:00.343                                  2147483647 1900-01-01 00:00:00.343
Any assitance appreciated.

View 1 Replies View Related

Problem In Datetime Function

Feb 6, 2007

hi, i got this function in the following link:

http://www.simple-talk.com/community/blogs/philfactor/archive/2006/03/26/710.aspx
i just copied the code and checked it gives sysntax error.so please how to use that function in my database,help me

View 2 Replies View Related

Datetime Function. How To Only Save Only The Time And Not Date

Mar 30, 1999

I would like to save only the time into the datetime field in a table. when I do this, It will save it as "1/1/1900 15:23:00" All I would like to have is the time! Is there a way to update it so that it will only save the time?

Thanks In advance!
/Mike

View 1 Replies View Related

Is There An Aggregate Function To Sum A Datetime Field Values?

Jan 15, 2004

Hi all...

how I can obtain the sum of a datetime field as aggregate function?

Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?

Thanks..

Massimo

View 7 Replies View Related

Assigning Datepart Function To A Datetime Variable?

Aug 12, 2006

I am getting wrong output when assigning a datepart function to a variable. I should get 2006 but instead I get an output 1905.

Below is the code and output. Any help will be greatly appreciated. Thanks

DECLARE @FiscalStartCurrYear datetime

SET @FiscalStartCurrentYear = DATEPART(year, GETDATE())

select @FiscalStartCurrYear

Output

-----------

1905-06-30 00:00:00.0000

View 5 Replies View Related

Datetime Function Glitch -- Same Settings, Different Results

Oct 23, 2006

hi guys,

i am encountering a pretty weird problem on our SQL database stored procs, particularly those concerning datetime functions. we have two setups, one is on london, and another one is here in singapore. both servers have the same regional settings, the same database tables, stored procs and functions, almost the same in every aspect. the only difference we know is that one is a direct database cut (sql 7.0), while the other used database migration in win2k.

here goes: all stored proc functions work just fine on the singapore setup. the london setup however, is not working at all! it does all the functions like change flags and status, but it doesn't update dates nor search for data by date. i have also checked on the collision names, and they are the same. where else could i start searching for discrepancies between the two?

hope to hear something from you guys, i'm getting desperate. thanks in advance!

View 5 Replies View Related

SQL Server 2014 :: Function To Convert Datetime To String

Jan 26, 2014

Writing a SQL Function as below

the input parameter for function should be datetime of sql datetimeformat

and out put should be a string = yyyymmdd1 or yyyymmdd2

The last character 1 or 2 based on below condition

if time is between 6AM and 5.59PM then 1
if 6PM to 5.59AM then 2

View 6 Replies View Related

SQL Function To Display A Seconds Counter In Datetime Format

Aug 28, 2007

I'm querying a database table that creates a time stamp in seconds only. I have a starting time of
1099725928 = 11/6/2004 12:25:28 AM. So that if another entry is made 1 second later the time stamp value entered into the table is 1099725929. The front end application does the converstion from the seconds counter to the datetime format. The query I am writing calls information from this table for a different application that does not have the conversion capability. If I know the starting point (I don't want to create a conversion table) which is 1099725928 = 11/6/2004 12:25:28 AM, is it possible to write into my query script a function to convert the seconds value to the correct datetime format? It would need to accurately account for leapyear and even/odd months.

************************************************************************************************
Select Case_ID_, Region, Assigned_To_Group_, Assigned_To_Individual_,
Status, Priority, Category, Type, Item, Affiliate, Hours_to_resolve, Resolved_Time, Assign_Time, Create_Time
From HPD_HelpDesk
Where Region = 'Central Valley'
and Assigned_To_Group_ = 'CVSA Desktop Support'
and Status In (1, 2, 3, 4, 5)
and Priority In (0, 1, 2, 3)

Order by Case_ID_ Desc************************************************************************************************

View 6 Replies View Related

DateTime.Now, An Allowed Null Field, And My Insert Function From My Dataset

Jun 6, 2008

I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.

Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()),
PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0);
The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting.
Any suggestions would be most appreciated

View 1 Replies View Related

Implicit Conversion From Data Type Datetime To Int Is Not Allowed. Use The CONVERT Function To Run This Query.

Mar 26, 2008

Hey im trying to store a category name and the date into a database. For some reason i keep getting this error
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
This error is the error im getting back from the database. the datetime field in the database is a datatype (DateTime) so what exactly is going on ?protected void InsertNewCat_Click(object sender, EventArgs e)
{                    string insertSql = "INSERT into Category (CategoryName,Date) VALUES (@Category, @Date)";
                    string conString = WebConfigurationManager.ConnectionStrings["ProCo"].ConnectionString;                    SqlConnection con = new SqlConnection(conString);
                    SqlCommand cmd = new SqlCommand(insertSql, con);                   cmd.Parameters.AddWithValue("@Category", NewCat.Text);
                    cmd.Parameters.AddWithValue("@Date",DateTime.Now);

try
{
              con.Open();             int update = cmd.ExecuteNonQuery();            CatInsertStatus.Text = update.ToString() + " record updated.";
}catch (Exception Err)
{
             CatInsertStatus.Text = Err.Message;
}
finally
{
             con.Close();
}
}

View 9 Replies View Related

Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server

Jul 9, 2007

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

View 3 Replies View Related

Inserting Datetime Through Sqldatasource - String Was Not Recognized As A Valid DateTime

Dec 6, 2006

I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005

View 1 Replies View Related

DateTime Unable To Save In Datetime Field Of SQL Database

Mar 14, 2007

Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated

View 4 Replies View Related

SQL Query: Finding Records Between Datetime Inside Datetime

Mar 17, 2007

Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark

View 2 Replies View Related

Datetime Data Type Resulted In An Out-of-range Datetime Value. Please Help

May 13, 2006

Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest

View 2 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

Retrieving A Datetime With A Time Of Midnight (from A Typical Datetime)

Sep 7, 2007

Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.

View 5 Replies View Related

Datetime W/ Format = D Still Showing Time Component Of Datetime

Jan 17, 2008

e.g.

1st March 2005 12:00:00

is showing as

01/03/2005 00:00:00

instead of

01/03/2005

Why does this happen?

View 4 Replies View Related

Comparing A Real Datetime To A 'constructed' Datetime

Jun 15, 2004

I have the following SQL:

select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a

where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.

The SQL above returns

2004-04-20 00:00:00.000 Deposit ...

Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

I get the following error:

Syntax error converting datetime from character string.

Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...

View 6 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

Transact SQL :: Difference Between Datetime In One Row And Datetime In The Row Above

May 21, 2015

I have a table that has a unique ID and a datetime of when something changed.

See example:
ID   TimeStamp
16094   2013-11-25 11:46:38.357
16095   2013-11-25 11:46:38.430
16096   2013-11-25 11:46:38.713
16097   2013-11-25 11:46:38.717
16098   2013-11-25 11:46:38.780

[Code] ....

Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.

View 10 Replies View Related

How To Get Starting Datetime(monday) Of The Week And Ending Datetime Of The Week(sunday)

May 2, 2007

hi friends,

how to get the date of the first/last day of the week in sql...

can any one help me

Cheers,

raj

View 5 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...

ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )

i hope to have the answer because i need it so much.

Maylo

View 7 Replies View Related

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

Jan 28, 2008

update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?

View 5 Replies View Related