Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Year Part From A Date And Time


Hi,
I have a table with field Msg_Time. In this field some records are there with date and time.

For eg:-
11/7/2006 2:35:00 AM,
11/1/2006 2:35:00 AM,
10/15/2006 2:35:00 AM,
11/7/2005 2:35:00 AM,
11/7/2005 2:35:00 AM,
8/26/2005 2:35:00 AM,
3/2/2005 2:35:00 AM etc

I wrote the query like this
"select datepart(year, Msg_Time) from C20_Messages"
This query result is:-
2006
2006
2006
2005
2005
2005
2005
Here I want to get only the year part without repeating.
I mean
2005
2006

How to do pls help me
with regards
shaji


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Only Month And Year From Date Time
Hi All,
 
How do I show only month and year from datetime data type field?
 
As a example; If the date is 01-10-2008.
I wanna show the date in my report as Jan - 08
 
Thanks

View Replies !   View Related
Inserting Just The Time Part Of The Date?
In the code below why does it not enter 10:00 AM but instead it enters: 1900-01-01 10:00:00.000 ?

Create Table MyDateTest
(
DateColumn datetime
)

insert into MyDateTest values ('10:00 AM')
select DateColumn from MyDateTest

--PhB

View Replies !   View Related
Date Part Of Date Time
SELECT ltrim(str(datepart(yyyy,getdate()))) +'-'+
replicate('0',2-len(ltrim(str(datepart(mm,getdate())))))+ltrim(str (datepart(mm,getdate())))+'-' +replicate('0',2-len(ltrim(str(datepart(dd,getdate())))))
+ltrim(str(datepart(dd,getdate())))

This is how i am getting datepart of datetime.Is there any other way to get the date and also time seperately..

Thanks.

View Replies !   View Related
To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View Replies !   View Related
'Dynamic Time(year,month,date)' Report Template (rdl) Using Report Designer VS2005
I have three types of specific reports that i have to create with the input parameters (range) either

1: By date (rdl 1)

2.By Month (rdl 2)

3.By Year (rdl 3)

Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).

 I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.

Thanks

Regards

Alu

View Replies !   View Related
Compare Only Month And Year Part In Datetime Type
hai friends,
iam doing a project in .net and using sql server.
i need to compare only month and year part in datetime type to retrive data.
1)retrive unique year and its months available in the database.
like may 2008
apr 2008
mar 2007

View Replies !   View Related
Fiscal Year Date Help??? I Need The First Day Of The Year To Be 01/27.
 
Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck

View Replies !   View Related
Extract Date,month, Year From The Date Getting From Sql Table
Hello All,
i have three textboxes in a page and i want fill those textboxes  with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne

View Replies !   View Related
Conversion Of Oracle Date Time To Sql Server Date Time In SSIS
This is driving me nuts..

 

I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.

 

I'm struggling with this for a quite a while and I'm not able to get it working.

 

I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511  23:06:30:000

 

the space in MM : SS is intentional here, since without that space it appread as smiley  

 

I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data

 

I'm struck with error for hours now.   Any pointers would be helpful.

 

Thanks

View Replies !   View Related
How To Do A &"year-to-date&" SQL Query Where &"year&" Commences In August?
Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View Replies !   View Related
Time Part Of Field
Hi,

I have a very beginer's question. I am trying to compare datetime field with time only. how can i just compare with time?

Select start_time,end_time from tblvouchers as sv,tblvoucherlines as svl
where sv.voucher_no = svl.voucher_no and
sv.equip_no = '01325' and svl.line_date ='03/14/2004'
and svl.start_time = '18:00' and svl.end_time = '00:00'

this query is not displaying any data.

Any help will be highly appreciated.

View Replies !   View Related
Remove Time Part
Does anybody knows how to remove the time part of the datetime format?

some tables in my database's using datetime format, and the time's not default (12:00:00 AM) but it save the exact time.
Example : 7/17/2007 11:02:06 AM

when i use datetime for parameters, there's no value returned. While it's impossible for users to entered the exact time when they want to preview reports.

So, how to remove the time part in datetime format?

Thanks!

View Replies !   View Related
How To Take Off The Time Part Of The Parameter
Hello,

How can I display data that only compare the date part (ignoring the time part) between the value of the date parameter and the database?.  It displays data only if I pass the whole date (date and time)

I tried with the following query in the report services 2000, but it even didn't pass the query parse

SELECT     *  FROM table

WHERE     (CONVERT(CHAR(8), table.PRODUCTION_DATE, 112)
                      = CONVERT(CHAR(8), @parameterDate, 112))

 

Any ideas will be appreciated.

Thanks,

Marco


 

View Replies !   View Related
Comparing Datime(SQL ) Year, Month, Day,Time
 hi    i want to compare tow dates in my procedure, comparing all(year, month, day, time).can anyone help me. thanhs.

View Replies !   View Related
Please Help With MDX - Year To Date
Hi,

I have 2 dimensions Time and Product....

If I stay at the Year level (say 2004), I get the Year To Date Sales for the selected Product(s)... However when I "zoom into" the Time dimension and select, say, Quarter 2, I get the Sales only for Quarter 2 of the selected Product(s). This is fine but at the same time I also want to see the Year To Date Sales of the selected Products. I hope my post is clear? Let me add a little example to clarify this further and will really appreciate your help:

Suppose I have selected ALL products and have selected Quarter 2 of 2004. I want the following results (Note it's really the YTD column that I am after):

Total Sales YTD Sales
----------- ----------
$ 50,000 $ 125,000


Again, I'll appreciate your help in writing the MDX for this.

View Replies !   View Related
How To Select The Date Part Of A Date
I use smalldatetime for a datetime and i just display the date part
i'd like to compare the date part of the smalldatetime and the date i have
how can i do that ?
I know we can select the day,month or year ...
If you know a link where i could find different kinds of example about sql command it would be great to

thanks

View Replies !   View Related
Point In Time Restore Part II
In the hereunder written message I talk about point in time restore.It is now based upon the fact that there are no hardware problems or what soever.I just would like to roll back to a situation of some time (minutes, hoursor what ever) ago.Used to the ingres database a point in time restore can take place UP toany, any, any time since the last FULL backup. (any time up to now !!!)I can't understand why a point in time restore can only be done based upontransaction log backups. The current transaction log is also available in myopinion. (Turn off the power, turn on the power and you will notice that theautomatic recovery is based upon this transaction log file; so in that casethis file is used)That's what my question is about. Is it correct that a point in time restorein a SQL server environment can only be done up to the last transaction logbackup.ByeArno de Jong,The Netherlands.

View Replies !   View Related
Removing Time Part Of GetDate()
I've been using the GetDate() function to populate a column in a table.
But it populates it with both the date and time: 2000-11-08 11:22:28

I'd like it to just put in the date: 2000-11-08

I've tried the Convert function as well as other functions, to no avail.
Some attempts have reversed month and day (I'm in Australia, so all PCs and Servers are set with a dd-mm-yy date format)

Any suggestions please?

View Replies !   View Related
Time Part Of Smalldatetime Field
I'm trying to extract the time part of a smalldatetime field in a usprocedure to return just the time. I've used Datepart() to get the hours or minutes but you can't get both without getting messy. Anyone know a way to keep it neat & simple?

Thanks, archie

View Replies !   View Related
Date Need Back Off Four Year
I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.

View Replies !   View Related
Date Is Less Than Year 2008
I have a Actual_dt column that is a type of date. I am trying to get all the values from the table that have the date less than 2008/01/01.

Please help.
Thanks

View Replies !   View Related
Year To Date Calculator
Does anyone know a script that will give the year to date? I'm doing a sales report and need to pull year to date information.

View Replies !   View Related
See If Date Is Same Year And Month And Day
Hi,

I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.

SO I need to do:

IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)

But it has to be comparing the year,month and day (excluding the time part of the datetime type).

View Replies !   View Related
Select Max Date Row For Each Year
Hi !
 
I've got a list like this one:
 









uniqueID
technicianID
StatusID
Date

1
1133
2
01-01-2003

2
1133
2
01-03-2003

3
1133
2
01-01-2004

4
1133
3
01-08-2004

5
1133
3
01-02-2007

6
1133
4
01-04-2007
 
 
i need to select only the last rows of each year :
 




2
1133
2
01-03-2003




4
1133
3
01-08-2004




6
1133
4
01-04-2007
 
How can i achieve that , i have tried with max, but it does not retrieve all the fields.
 
thanks
 
 
 

View Replies !   View Related
How To Test The Time Part Of A DateTime Field
 

Dear all,
 
I have an application where I need to implement a Team Shift management.
 
The team shift table need to be defined as follow :
 
Shift table
=========
 
Id             DateTime       IsCurrent
1              08:00:00              True
2              16:00:00              False
3              00:00:00              False

 
From the table above the current running shift is Id = 1
 
Based on that I have following question:
 
1 - Is there a way to specified for a DateTime field that it will recieved only Time part ?
 
2 -  When a shift change occurs, I need to compare the Current time with the time shift in the table. Then according to that I set the IsCurrent flag to proper new Shift ID.. how to perform this ?
 
Thnaks for your help
regards
serge

View Replies !   View Related
Access To SQL Server Via WCF Works Only Part Time
We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity. 

 

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

 

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

 

Cannot open database 'Talker' requested by the login. The login failed.

Login failed for user 'AcornCommunicationServices'.

 

The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user.  master has been set as the default database for the user.

 

Basically, as far as I can see Talker and Guider are configured identically!  So I cannot figure out why I cannot login to the second database!

 

Is there a specific setting I'm missing somewhere to grant login access to the user?  I'm using

Management Studio Express to manage the database.

View Replies !   View Related
Is It Possible To Append The Current Year To The Numeric Primary Key At The Time Of Inserting The Re
Hi,

I need help to resolve the following issue. I have one numeric primary key in table. I want to append current year to that primary key at the time of inserting the record. This works fine in single user environment. But it fails in multiuser environment. I mean when two people try to insert the record simultaneously.

For Example: RegId (primary key)

RegId : 122002 (12 is the number and 2002 is the current year)
This helps to track the no of users for the current year and also i can avoid one extra field for year in the table.

Please help me........

Thanks & Best Regards
Mushtaq

View Replies !   View Related
Last Year Month To Date Function
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***

View Replies !   View Related
Subtract A Year From Current Date
Hey all, how do you take the current date and subtract a year from it in a SP?

What I want to do is...

Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.

Edit: the answer is..

dateadd(yyyy, -1, getdate()) as Date1

View Replies !   View Related
Want To Concatenate Year And Month To Get A Date
Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View Replies !   View Related
Want To Concatenate Year And Month To Get A Date
Hi,

My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

I'll appreciate your help.

YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....


Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

View Replies !   View Related
MDX Question About Getting Year To Date Sales
Hi,

I have 2 dimensions Time and Product....

If I stay at the Year level (say 2004), I get the Year To Date Sales for the selected Product(s)... However when I "zoom into" the Time dimension and select, say, Quarter 2, I get the Sales only for Quarter 2 of the selected Product(s). This is fine but at the same time I also want to see the Year To Date Sales of the selected Products. I hope my post is clear? Let me add a little example to clarify this further and will really appreciate your help:

Suppose I have selected ALL products and have selected Quarter 2 of 2004. I want the following results (Note it's really the YTD column that I am after):

Total Sales YTD Sales
----------- ----------
$ 50,000 $ 125,000


Again, I'll appreciate your help in writing the MDX for this.

View Replies !   View Related
Find The Date If I Know The Week And Year
Hi Guys,
Can anyone please let me know How to find the first day of the week, if I know the weekno and the year.
For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week
for the above query the date will be eg:26/06/2003

I really appreciate your Help

Thanks:confused: Find the Date if I know the week and year

View Replies !   View Related
Converting Year/week/day To Date
i have this SQL table with a year, week and day-of-week field, but no field containing the actual date

is there a way (e.g. in a view of the SQL table) to transform the year/week/day combination into a date string (e.g. format dd/mm/yyyy) ?

View Replies !   View Related
Date In Current Year Or Last Two Months
Hello
 
I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.
 
So, this is what I have.......
 



Code Snippet
 
where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
   convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end
 
 



Is there a neater/better way of coding this?
 
Jon

View Replies !   View Related
Using A Year Parameter In A Date Condition
I am collecting a year parameter from my user and want to use that year as part of a complete date condition.

So I collect the year say '2003' from my user.

A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.

I am currently using the following logic:

 (DATEPART(yy, CANCELLATION_DATE) = @Year)
AND
(DATEPART(mm, CANCELLATION_DATE) >= 04)
AND
 (DATEPART(dd, CANCELLATION_DATE) >= 02)

This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02.  What I need is to gather all dates prior to Apr 02, with the year paramter to follow. 

We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there!  Anybody know of one? 

Here is how the pl-sql is structured:
CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') )  ) )

Thanks in advance,
mpaskevi

View Replies !   View Related
Date Part
Hi,

i am trying to write a query the pulls out a table which has date information stored as dd/mm/yyy but all i want is the month and the year.

So i tried

select datepart(mm, dtinsertdate)
from incident

which only gives me month and also used

select datepart(year,dtinsertdate) [year], datepart(month, dtinsertdate)[month],

Which works but i want both in the same column.

But when trying to get both mm, yyyy it looks like it only supports one datepart. Is there any way aroung this?

Many Thanks

John

View Replies !   View Related
Part Date In Sql
Hi,

I think this is a simple question but I am just not sure how to do it right, as a newbie.

All I want to do is generate a report of company employees grouped by their date of hire.

SELECT DateOfHire, FirstName, LastName from Empls
ORDER BY DateOfHire

The only problem is that the DateOfHire displays as, e.g., '01/03/2004 12:00:00' which is not what I want.  I SIMPLY WANT TO OMIT THE TIME PART but don't know quite how to say this.  I tried several variations along this line but it causes scoping errors...

SELECT     CONVERT(Varchar(10), DateOfHire, 101) AS DOHDateOnly, FirstName, LastName, State
FROM         EMPL
GROUP BY DOHDateOnly

but this gives error

"The value expressions for the textbox ‘DateOfHire’ refers to the field ‘DOHDateOnly’.  Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Which makes sense, but I don't know HOW to say the right thing to get what I want.

Any help will be greatly appreciated.  

View Replies !   View Related
Extract Month & Year From Date Column
hi

i have column in database as account open date

format as:Jan 27,2004 12:00:00:AM

How do i extract month& Year from this column..
all i have to do a calculation

if accountopendate is prior to dec 31 1994 then jan 1995..

and if the account open date is after 2100 then ist jan 2011.

how do i write the calculation

Thanks guys
phani

View Replies !   View Related
Select Date Year Below 1950 Not Valid?
i am doing a select statement, the .sdf database file has dates from 1910 to 2020

Dim fullcurrent_date As Date = New Date(1950, 10, 2)
Dim sqlstr As String = "SELECT * FROM table WHERE Start_Month <= '" & fullcurrent_date & "'"

it seems to work alright, but if i were to initialize the Date as

Dim fullcurrent_date As Date = New Date(1949, 10, 2)

it doesn't seem to work, is there a limit somewhere?? as long the year is below 1950 the select statement seems to be having problem searching for that line.

Thanks

View Replies !   View Related
Date Part Of Getdate
i want to use the date part of the function getdate in my sql query.
the getdate function return the current date and time. i want only the date part.
how can i do it.

View Replies !   View Related
How To Get The Date Part Of Datetime
Is there any other way which is more simpler(shorter) to get the datepart (I don't want the time part ) of Datetime than this ?Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As[Date]

View Replies !   View Related
Date As Part Of Primary Key?
I have three items which make up the primary key:
ProjectNumber, Hardware, Date

Problem: The Date is formatted with date and time, how can I add a record and use just the date?

Currently I use GetDate() in my stored proc.

Mike B

View Replies !   View Related
Searching For Only Part Of A Date
Ok, so I have a Select statement that looks like this:

Select * from FilteredOpportunity
where (OpportunityID LIKE @opportunityid)
and (Name LIKE @name)
and (CustomerIDName LIKE @customeridname)
and isNull(EstimatedCloseDate, '1900-01-01') LIKE @estimatedclosedate
and isNull(Description, '') LIKE @description
and isNull(AccountIdName, '') LIKE @AccountIDName
and isNull(ACCU_OppTypeName, '') LIKE @ACCU_OppTypeName
and isNull(SalesStageCodeName, '') LIKE @SalesStageCodeName
and isNull(ACCU_CustomerTypeName, '') LIKE @ACCU_CustomerTypeName
and (CreatedByName LIKE @CreatedByName)
and (CreatedOn LIKE @CreatedOn)
and isNull(ACCU_OppSourceName, '') LIKE @ACCU_OppSourceName
and (StateCodeName LIKE @StateCodeName)
and (StatusCodeName LIKE @StatusCodeName)

This chunk of code is used in an XML file which, once this XML is imported (using sharepoint by the way if you haven't noticed), I can then search through tables with different filters. Well, my date filter isn't working the same way as my other ones. I have a couple different date filters, but none of them work.

Before I ask my question, I only pass in one variable at a time through my code.... the rest of the variables get a default value passed to them if the user doesn't pass it. That value is simply '%'. Anyways, so the different ways a user can type in a date is by typing in the whole date, part of the date starting with the beginning of it, typing in the end of the date, or typing something within the date. For a string it's like 'Example', 'Exam%', '%ple', and '%amp% respectively. How do I accomplish this with a date?

View Replies !   View Related
Hwo Do We -- The Date Part Of --- Getdate() '+' '16:00:01'
Need some help in getting the following.. (Looking for a Expression)
 
The date part of --- getdate() '+'  '16:00:01'

 
Basically i am looking to get the records based on a DATETIME Column UPTO 4 PM Server that paricular day.

View Replies !   View Related
Getting Date In Day/month/year Instead Of Month/day/year
I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.

I'm using a DatePart function that converts my date:


CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END


This returns te american version, can anyone help me to get this to convert the UK way.

Thanks

View Replies !   View Related
Creating Birth Date From Day, Month, Year Fields
I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)

They are populated like this (1985, 12, 21)

I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.

The code

UPDATE DOB
SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

Returns 6/11/1905 for 1959,11,17

View Replies !   View Related
How To Get Date Difference In Terms Of Year,month,days
 

I am using oracle 10G DB as back end.I have two date fields in a table.
 

1)premium_paying_start_date

2)premium_paying_end_date

 
i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).

The difference should show the year,month and no of days difference.

For example :

premium_paying_start_date : 14-10-1984

premium_paying_end_date : 01-03-2008

Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)
 

So please give me the solution for this.

View Replies !   View Related
Date Query Problem Last 8 Days, Different Year From Current
Hi,
 
I'm having problems creating a query that brings the results based on last 8 days on date column (column9) and diferrent year from the current one.
 
If I run the each filter,
 



Code SnippetYear(Column9) <> Year(GetDate())
 
 


and
 



Code Snippet
(Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
 
 

it returns the right values.
 
So far I've got (it returns no results):
 



Code Snippet
SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14,
Column15, Column16, Column17, Column18, Column19, Column20, Column21, Column22, Column23, Column24, Column25, Column26, Column27,
Column28, Column29, Column30, Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,
Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, data_anulacao, data_instalacao
FROM Contratos01
WHERE Year(Column9) <> Year(GetDate()) AND (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
ORDER BY Column1
 
 
Please give me your input.
 
THX.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved