Datepart(wk,date) Problem: (urgent)
Jan 9, 2008
I am Pulling some data from a table including the date. Then I insert these data into another table including the week number
datepart(wk,td_date)
here is the problem:
the week starts on Monday and ends on Saturday
from
12-31-07 to 01-05-08
So the value for week should be 1
What I get is:
Date Week
12-31-07 53
01-01-08 1
01-02-08 1
01-3-08 1
01-04-08 1
For 12-31-07 the value should be 1 for the week.
Here is my insert statement:
insert into B
(Week,date)
select datepart(wk,date),date
from A
your help will be appreciated
Thanks
View 12 Replies
ADVERTISEMENT
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
Nov 15, 2007
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
View 8 Replies
View Related
Dec 17, 2007
Dear All expertise please help me becuase I am very beginner
I have a Holiday Table in which contain each employee's holiday from date and todate . I want to get an sql to get the record having holiday from the given date range
example
Employee A having holiday from 01-01-2008 to 06-01-2008
Employee B having holiday from 05-01-2008 to 10-01-2008
Employee C having holiday from 20-01-2008 to 30-01-2008
I given the criteria as follows to get list of employeed having holiday in given date range
Holiday from 01-01-2008 to 06-01-2008 . So Employee A and Employee B should come in the list. Please give me an SQL
View 5 Replies
View Related
Jan 11, 2007
I have a table that hold a couple of fields.
STARTDATE - The day the service starts
FREQTYPE - This field tells me how often a schedule occurs. Here are the vaules -1=oncall, 0=days, 1=weeks, 2=months
FREQPERIOD - This is the number of days, weeks, or months between services. a value of -1 would indicate being oncall.
DayOfWeek, Day of week for week or month-based frequencies 0=sunday 6=saturday -1=if on call
WEEKOFMONTH - week of month for month based frequencies 1-4, value of 5 if it the last week of the month,
So what I need is for this report to look at the start date then figure out what the frequency is (days, weeks, months) then look at the frequency period (how many days are between services) what day of the week it falls on and list each service date on a report. There is an example at the bottom
I have tried numerous things and I can't get it and I need this asap today. I can provide what ever anybody needs, Many thanks.
Here is an example of the output that I need.
Service Frequency Every 12 weeks or 84 Days
Pickup Starting Point 1/4/2007
Pickup Schedule
3/29/2007
6/21/2007
9/13/2007
12/6/2007
2/28/2008
5/22/2008
8/14/2008
11/6/2008
1/29/2009
4/23/2009
7/16/2009
10/8/2009
12/31/2009
Here is sample data with the relationships
http://www.balzoutonline.com/reporting/database.jpg
Here is the service table schema
http://www.balzoutonline.com/reporting/service.jpg
Here is the serviceinfo table schema
http://www.balzoutonline.com/reporting/serviceinfo.jpg
Here is the serviceschedule table schema
http://www.balzoutonline.com/report...iceschedule.jpg
And here is the site table schema
http://www.balzoutonline.com/reporting/site.pdf
Here is something that was created in excel wich is pretty easy but a manual process. This is what I would like to replicate in crystal
http://www.balzoutonline.com/reporting/Schedule.xls
View 1 Replies
View Related
Nov 12, 2007
Hi,
I am using a sql database and vb.net... In form i display a date from calendar as smalldatetime in the format of dd/MM/yyyy by changing the web.config file to culture = "en-GB"... It did display as dd/MM/yyyy with no doubt... Problem is in sql database it is display as mm/dd/yyyy, there4 when i retrieve it out, it is not the format i wan... so can any1 helps mi? I need do many adding and subtracting to the date so a constant dd/MM/yyyy format is veri important to mi... I cant save it as yyyy/mm/dd cos my boss dun wan it... If use Convert method when i select * out how to convert the date zzz
Helps would be greatly appreciated
View 9 Replies
View Related
Jan 15, 2008
Hello all ..
I have a form that views the date automatically in a textbox... the date of this text box must be stored in the database after the user press the submit button ... However, I am getting a wrong date which is 01/01/1900 .. although I see it in the form as 15/01/2008 ..
this is the code ... please give me a solution ASAP ..
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
'Declaring the variablesDim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim courseId, traineeName, traineeId, department, comment1, comment2 As String
Dim formDate As Date
Dim RB1, RB2, RB3, RB4, RB5, RB6, RB7, RB8, RB9, RB10, RB11, RB12, RB13, RB14 As String
Dim ConnectionString, evaluationSQL, TraSQL, iTotal As String
'Save form values in variables
formDate = Convert.ToDateTime(DateTxt.Text)
courseId = CourseIDTxt.Text
traineeId = EmailTxt.Text
traineeName = TraineeNTxt.Text
department = DeptDropDownList.SelectedValue
RB1 = RBL1.SelectedValue
RB2 = RBL2.SelectedValue
RB3 = RBL3.SelectedValue
RB4 = RBL4.SelectedValue
RB5 = RBL5.SelectedValue
RB6 = RBL6.SelectedValue
RB7 = RBL7.SelectedValue
RB8 = RBL8.SelectedValue
RB9 = RBL9.SelectedValue
RB10 = RBL10.SelectedValue
RB11 = RBL11.SelectedValue
RB12 = RBL12.SelectedValue
RB13 = RBL13.SelectedValue
RB14 = RBL14.SelectedValue
comment1 = CommentTxt1.Text
comment2 = CommentTxt2.TextConnectionString = WebConfigurationManager.ConnectionStrings("GRPConnectionString").ConnectionString
'Create and open the connection objConnection = New SqlConnection(ConnectionString)
objConnection.Open()evaluationSQL = "Insert into Evaluation (Eva_Date, CourseD_Id, Tra_Id , Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Comment1, Comment2 ) " & _
"values(" & formDate & ",'" & courseId & "','" & traineeId & "','" & RB1 & "','" & RB2 & "','" & RB3 & "','" & RB4 & "','" & RB5 & "','" & RB6 & "','" & RB7 & "','" & RB8 & "','" & RB9 & "','" & RB10 & "','" & RB11 & "','" & RB12 & "','" & RB13 & "','" & RB14 & "','" & comment1 & "','" & comment2 & "')"objDataCommand = New SqlCommand(evaluationSQL, objConnection)
objDataCommand.ExecuteNonQuery()
View 3 Replies
View Related
Oct 10, 2005
Hi, I have a query as follows:select transactionDate, (select count(transID) from ......)I am having a problem which is that dates are not shown uniquely.For example,oct 1, 2005 00:12:00 - 12oct 1, 2005 01:30:00 - 12oct 1, 2005 01:13:00 - 12I want only to have:oct 1, 2005 01:13:00 - 12The select is working on time and not on a date, can you help me please?thank you.
View 1 Replies
View Related
May 4, 2001
Hello All,
I've a column of Date/Time type 'SenOn' in which NULL is allowed. If I enter some date in that column, and then next time again I want to make it empty (NULL I mean) there is an error that this value cant be changed.
Please note that there are no dependencies, so that is certainly not the problem.
Please help me this is really urgent.
View 2 Replies
View Related
May 20, 2001
Hi there,
I want to display month name from a date field.
Eg:
May from 5/21/2001
without using Case statement.As i want to group by data using month name.
Thanx for Reading it.
View 1 Replies
View Related
Jul 20, 2005
Hi,What I have is a booking table, and when updating/inserting I need toensure that there are no date/time overlaps. The problem I'm having isthat while the following script works for events on the same day, itfails miserably when a booking starts on a previous day.I've just spent the last hour going through previous posts and justcan't seem to it right.My DB structure (Sql Server 2000):Table: CollateralBooking-- CBID - int, identity(1, 1)-- CBcPartNumber - varchar(50) (foreign key)-- CBdDateTimeFrom - smalldatetime-- CBdDateTimeTo - smalldatetime-- CBcAlias - varchar(50) (foreign key)My current script (in a stored proc):IF (SELECT COUNT(*) FROM CollateralBookingWHERE (((@CBdDateTimeFrom > CBdDateTimeFrom) AND (@CBdDateTimeFrom < CBdDateTimeTo))OR ((@CBdDateTimeTo > CBdDateTimeFrom) AND (@CBdDateTimeTo < CBdDateTimeTo)))AND (CBcPartNumber = @CBcPartNumber)) <> 0BEGIN-- Return an error.END-- ... Other checks & finally, the insert/update.--Posted via http://dbforums.com
View 2 Replies
View Related
Dec 12, 2007
I have a date "4/25/2007"
but the requirement is to have data as "04/25/2007"
.if month is single digit it should have leading zeroes, same with date,
can some tell me how to convert this.
View 2 Replies
View Related
Dec 12, 2007
I have a date "4/25/2007"
but the requirement is to have data as "04/25/2007" in datamart.
if month is single digit it should have leading zeroes, same with date,
can some tell me how to convert this.
View 7 Replies
View Related
Nov 13, 2007
Hi, How can i calculate the date out? i am using vb sql server database...db saved my date as dd/MM/yyyy, form display my date as dd/MM/yyyy too
1.I have a selected date by user from calendar in tb_dop.text
2.creditDate.text for user to enter number of days to add to tb_dop.text date
3.dDate.text to display the calculated date Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
'display selected date from calendar
tb_dop.Text = Calendar1.SelectedDate()
End SubPrivate Sub btn_add2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add2.ClickDim myDate As New DateTime
myDate = tb_dop.Text
Dim i As Integer
i = creditDate.TextDim dDate As New DateTime
dDate = myDate.AddDays(i)
dueDate.Text = dDate
ERROR
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.The statement has been terminated.
I know it is becos diff format of date calculation, there4, how can i change it to calculate in mm/dd/yyyy format? Funny rite? i can saved and display in dd/MM/yyyy but i cant calculate using this format
Note: i cant change my form display format to mm/dd/yyyy cos i need it to be user friendly in my country ty (URGENT)
Once again thanks
View 4 Replies
View Related
Jun 18, 1999
I have a text file like this :
K045|13/03/1997
When I upload this text file to SQL Server 6.5, with the following fields :
Code Chraracter 4
Tdate Smalldatetime
I use BCP to upload this text file to SQL tables, but I got a wrong date,
like :
Text file :
K045|13/03/1997
SQL Tables data :
K045 Nov 19 1935 7:00PM
Can anyone on the net can help me ?
Thank's
View 1 Replies
View Related
May 9, 2001
I HAVE ONE COLUMN CONTAING THE INTEGER TYPE DATA COMING in UNIX DATE FORMAT. NEED TO CONVERT IT TO THE SQL SERVER DATE TIME FORMAT.
NOTE: UNIX IS STORING THE DATE IN SECONDS STARTING FROM 1970/1/1
ANYBODY CAN HELP ME TO SOLVE THIS PROBLEM.
e.g. The UNIX date time is 973101026 need to be converted to SQL DATE TIME
View 2 Replies
View Related
Aug 13, 2001
Hi all,
I'm trying to create a temporary table with one of the columns with a datetime datatype. Sql server 7.0 is giving me an error:
Error 195 date is not a recognized function name.
Example of my create table looks something like this:
create table #temp_table(column_one int, column_two(15,2), Column_three datetime)
View 2 Replies
View Related
May 7, 2004
Hi,
The subject say it all :)
I have a field which stores date which the datetime datatype... The problem is that I am also getting time information in this stored field.
I just want to extract the date part in dd-Mmm-yy format (e.g. 07-May-04). I tried convert (char (8), MyDateField, 112) but it gives me date in yyyymmdd format. How do I get the results in dd-Mmm-yy format instead?
Thanks.
View 7 Replies
View Related
Jan 31, 2008
I have a datetime field in a sql db named "arrdate". For what I am doing I only need to extract the date formatted as mm/dd/yyyy. Can someone give me an example of the proper syntax?
Something like seems like it should work:
SELECT id, DATEPART(mm/dd/yyyy, arrdate)
FROM guest
but of course it doesn't.....
View 3 Replies
View Related
May 7, 2001
Why would this return a year of 1905: If @tempdate was a varchar
it would return just 2001, but by it being a dateime it returns
the year 1905.
declare @tempdate datetime
select @tempdate = (select datepart(yy,getdate()))
select @tempdate
View 4 Replies
View Related
Mar 18, 2006
Datepart function is different from general ms using in MSSQL
I need something like this
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])
I try to get which week of year.. but first day of week for some country is not sunday..
How can I determine that the first day of week is monday?
View 1 Replies
View Related
Apr 21, 2008
CREATE PROCEDURE [dbo].[sp_TRAK_PROG_TOTALS]
@Frequency varchar (1),
@Rpt_Yr smallint,
@Prog_Yr varchar (2)
AS
SET NOCOUNT ON
DECLARE
@PROG1VARCHAR (20),
@PROG2VARCHAR (20);
--@PROG_YR VARCHAR (2);
SET @PROG1 = 'TRAK08'
SET @PROG2 = 'TRAK208'
SET @PROG_YR = Select DatePart(YY, GetDate()) as Current_Year
SELECT @Rpt_Yr = case @Frequency
WHEN 'M' then (select dateadd(mm,datediff(mm,0,getdate())-1,0))
WHEN 'O' then convert(varchar,@Start_Date,101) + ' 00:00:00'
ELSE convert(varchar,getdate()-1,101)
end
--------------------------------------------------------------------------------------------------
I have done most ot the script for the rest of my pgoram but I am having problems with the date requirements.
I was told to parse the year so that the user can enter the 2 digit date.
When it is 'O' (other) was told build a string to parse the year using datepart so that the user can enter the program year (2 digit format). (@Rpt_Yr)
When it is 'M' (monthly) then I am to goto to table tk_prog get the active program
I have no clue how to correct the above.
View 2 Replies
View Related
May 31, 2008
Hi All,
i'm trying to format SQL so that I retrive the day of the week and the hr in the same column.
SELECT Datepart([hour], Time) as Hour, SUM(Total) as Sales, count(TransactionNumber) as Customers, SUM(Total)/count(TransactionNumber) as 'Ave Sale'
FROM [transaction]
WHERE time between '05/30/2008' and '05/31/08'
GROUP BY datepart([hour],Time)
Output desired is
05/30/08 09
05/30/08 10
05/30/08 11
...
View 1 Replies
View Related
Mar 9, 2006
Hi , I am converting a datetime field to a string. The column is called DateScanDate.
This is my query;
SELECT CAST(DATEPART(Year, DateScanDate) AS VARCHAR(4)) + CAST(DATEPART(Month, DateScanDate) AS VARCHAR(2))+
CAST(DATEPART(Day, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Hour, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Minute, DateScanDate) AS VARCHAR(2))FROM HAAneurysmScan
I would like the month of March to be '03' instead of '3' and the 9th day of the month to be '09' instead of '9'
How can I do this?
regards
ICW
View 9 Replies
View Related
May 3, 2007
I would like to take the following code and display the data / count by month. I want to see how many people are logging in by month.. I tried using the datepart but I keep getting an aggrefate comannd error can anyone help modify this query
SELECT DISTINCT Count(login.login_time) AS CountOflogin_time
FROM login
WHERE login.login_time>=#10/1/2005#;
Thanks,
View 4 Replies
View Related
Sep 28, 2007
Using MS Reporting Services 2005
I have this expression in my field in my table and in my group by expression field
=Monthname(Datepart("m", Fields!CreatedDate.Value))
ok this gives me the month eg July etc
=Datepart("yyyy", Fields!CreatedDate.Value)
ok this gives me the year eg 2006 etc
if I try to add the two together I get the error (not in correct format) obviously as one is numeric and one alpha
=(Monthname(Datepart("m", Fields!CreatedDate.Value) & " " & (Datepart("yyyy", Fields!CreatedDate.Value))))
or I can do this:
=(Datepart("m", Fields!CreatedDate.Value) + (Datepart("yyyy", Fields!CreatedDate.Value)*100))
which gives me 200607
what do I need to do to have this show as
July 2006 etc
thanks
Dianne
View 4 Replies
View Related
May 30, 2008
How can I concat. these two into one column?
datepart(mm,DateCol), datepart(dd,DateCol) as MyDate
thanks.
View 7 Replies
View Related
Oct 9, 2007
Hi everyone, Im currently using SSRS 2000. I have a report that pulls data since 2001. I used Datepart('m", Fields!Shipped_Date.Value) to break it down into months. It works except that it doesnt separate the year. It'll group all of january together but 2001,2002,2003, etc, together. Then when I used Y to break it down by year, it does break it by year but keeps all the months together.....well what I want is to be able to have it jan 2001, feb 2001, jan 2002, separately group. Hope I didnt confuse anyone. All pointers welcomed.
Thanks,
Abner
View 1 Replies
View Related
Aug 22, 2006
I am wanting to set any given @Date parameter to the most current recorded Monday in a table (tblMarketPricing). Would this work:
@Date = Max(Datepart(dw,MktDate) IN 2
I am unsure if you can use Max with the Datepart function.
***If this is not allowed, can anyone suggest anything different that I might try?
View 3 Replies
View Related
Jan 31, 2008
ok, following up on my previous post that I marked as answered a little premature. The query below works fine in sql studio:
SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate
FROM guest
but when I try to use it in a c# code behind file:
comm = new SqlCommand("SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest WHERE id = @id", conn);
it bombs??
View 5 Replies
View Related
Feb 22, 2005
I'm trying to get just the day part of the date - 2/22/2005 (getdate()) but instead of returning '22', it's returning '2'. Can someone please tell me what I'm doing wrong?
Thanks!
Lynnette
Here's the code
declare @thisDay varchar
set @thisDay = Convert(varchar, Datepart(day, getdate()))
View 3 Replies
View Related
Apr 9, 2006
Hello dears;
I wanted to use DatePart function in an SQL statement using objectDataSource to extract the year only from a Date field, then populating only the years in Dropdown list, but it didn't worked. Is is possible to to use that function in an SQL statment.?
Thanks alot
VWD 2005
View 2 Replies
View Related
Aug 24, 2001
I am updating a demo system and need to bring all the dates with 1998 to 2000 and 1999 to 2001.
Im trying something like this but cant get it work.
UPDATE mytable SET datepart(yyyy, mydate) = '2000' WHERE mydate BETWEEN '01/01/1998' AND '12/31/1998'
How can I get this to work, or am I totally off?
Thanks,Adrian
View 4 Replies
View Related
