Year Part From A Date And Time

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:-
Here I want to get only the year part without repeating.
I mean

How to do pls help me
with regards

View Replies


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

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


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())))))

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


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,

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.




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.

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 to extract the date, month, year from this format and put the value in textboxes..?
Any help..

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,



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.



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


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?


View Replies View Related

How To Take Off The Time Part Of The Parameter


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

                      = CONVERT(CHAR(8), @parameterDate, 112))


Any ideas will be appreciated.




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


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


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.

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


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:







i need to select only the last rows of each year :



How can i achieve that , i have tried with max, but it does not retrieve all the fields.

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

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


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

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 ***

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


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
and so on ....

Many thanks in advance. I'll appreciate your help
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

Copyrights 2005-15, All rights reserved