hi all,
in my sql server 2005 , i 've a table gg containing a column 'date'.its data type is datetime ,null
now i want to insert only the date to this field from vb.net page. i dont want "time" to insert in this page. one more thing ,i know varchar will work for what i need but i want to use date time. If anyone who knows how this is possible pls do send me the code.
thanks swapna
Calculating time between two date In Sql Server 2005help me ..
View 3 Replies View RelatedGreetings,I have an e-timesheet application.i made a function that takes the startdate of the week:public SqlDataReader GetProjectsActive(DateTime SheetDate) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]); SqlCommand myCommand = new SqlCommand("GetProjectsActive", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter parameterSheetDate = new SqlParameter("@SheetDate", SqlDbType.DateTime); parameterSheetDate.Value = SheetDate; myCommand.Parameters.Add(parameterSheetDate); myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection); return dr; } i run the application and i pass the value 6/1/2008 (date without time) to the GetProjectsActive but what i notice that the storedprocedure adds the current time to the passsed date when it has been executed so some projects will not appear since the startdate and enddate of the project are only dates so i try this solution: parameterSheetDate.Value = SheetDate.Date;and it works finebut my question why the current time has been added to the storedprocedure automatically although i pass only the date 6/1/2008 date????your help is highly appreciated.Best regards.
View 6 Replies View RelatedThis 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
Is the Date/Time of the latest SQL Server 2005 instance restart stored somewhere in the system catalog?
This value is quite relevant when retrieving information on the costliest unused indexes in the instance.
I know of one way of doing this, which is to search the SQL Logs for this info.
This solution is very unwieldy - we recycle the logs daily and we only keep the logs of the last 7 days.
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I am trying to generate a report that is basically a class list. It lists classes for a specific semester and lists them by day of the week and then by time.
If I am pulling back a dataset of all classes, how do I filter my report so as to show each set of classes appropriately (by day and by time within the day)? I tried using the Filter function on the table level, but I guess I don't get it.
My table would need to look like this:
MONDAY:
8:30 - 9:30
9:30- 11:30
11:30-1:30
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
TUESDAY:
8:30 - 9:30
9:30- 11:30
11:30-1:30
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
classnum/prof
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
Thanks,
Steve
Hi folks,
I posted the following question on comp.databases.ms-sqlserver,
bu, to my surprise got no responses. So I wondered if you good
people might be able to help us out...
==================================================
Our application communicates with SQLServer 7 through ODBC. In
order to write date/time fields into the database, it uses either the dt
conversion function, or more usually the ts conversion, on a string of
the format 'yyyy-mm-dd hh:mm:ss.xxxxxx'. With almost every every
other database engine (I think Informix is the only exception), this is
accepted and converted into a date format that the back end can store.
With SQLServer 7, any inserts containing dates are dropped because the
timestamp conversion isn't working. If I take a copy of the select statement
into the query analyser, it reports an error unless I change the timestamp
string format from ts 'yyyy-mm-dd hh:mm:ss.xxxxxx' to ts 'yyyy-mm-dd
hh:mm:ss.xxx', ie from microsecond to millisecond resolution.
Version 6.5 didn't have this problem, and as I mentioned, nor does any other
database engine that our application talks to.
================================================== ===
Anyone any ideas???
Cheers,
Geoff.
I have been searching the net to see how to do this. I'm a noobie to DB's and I may have missed it. OK I have a Customer table and what I want to do is have a column with the date and time that the customer was added to the database ( i.e. Customer Since ) also last time that record was edited. Possible??
Thanks in advanced
Brad Nichols
Is there a better way of doing this?
DECLARE @PriorDayDate as varchar(10)
DECLARE @CurrentDayDate as varchar(10)
DECLARE @StartHourTime as varchar(10)
DECLARE @EndHourTime as varchar(10)
DECLARE @PriorDayDateANDStartHourTime as varchar(20)
DECLARE @PriorDayDateANDEndHourTime as varchar(20)
DECLARE @CurrentDayDateANDStartHourTime as varchar(20)
DECLARE @CurrentDayDateANDEndHourTime as varchar(20)
SELECT @PriorDayDate = convert(varchar,(GETDATE()-1),126)
SELECT @CurrentDayDate = convert(varchar,(GETDATE()),126)
SELECT @StartHourTime = convert(varchar,(dateadd(hh, -1, getdate())),108)
SELECT @EndHourTime = convert(varchar,getdate(),108)
SELECT @StartHourTime = left(@StartHourTime, 2) + ':00:0000'
SELECT @EndHourTime = left(@EndHourTime, 2) + ':00:0000'
SELECT @PriorDayDateANDStartHourTime = @PriorDayDate + ' ' + @StartHourTime
SELECT @PriorDayDateANDEndHourTime = @PriorDayDate + ' ' + @EndHourTime
SELECT @CurrentDayDateANDStartHourTime = @CurrentDayDate + ' ' + @StartHourTime
SELECT @CurrentDayDateANDEndHourTime = @CurrentDayDate + ' ' + @EndHourTime
--select @PriorDayDate
--select @CurrentDayDate
--select @StartHourTime
--select @EndHourTime
--select @PriorDayDateANDStartHourTime
--select @PriorDayDateANDEndHourTime
select cast(@PriorDayDateANDStartHourTime as datetime) as 'PD Start Time'
select cast(@PriorDayDateANDEndHourTime as datetime) as 'PD End Time'
select cast(@CurrentDayDateANDStartHourTime as datetime) as 'CD Start Time'
select cast(@CurrentDayDateANDEndHourTime as datetime) as 'CD End Time'
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?
hi guys
im having real problems and dont know how to solve it at all
i have a web app which allows users to enter information through edit boxes
when they submit the imformation it gets added into my SQL database.
does anyone know how to get the Date and Time when they insert the information and store in another column of type datetime in SQL database
the web app is written in C#
hope someone can help
thanks
My data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
I want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
I need to change one of the column to run utctime 2am. Whenever I declared and set the time, it has to run and set up to the UTC 2 am time.
How to declare the variable like that?
If I run that now, it has to set up next utc 2 am time, If I run after 2 hrs also or tomorrow whenever, it has to set the next utc 2am time
I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.
View 5 Replies View RelatedIf I have a datetime field, RequestDate, and a time field, DecisionTime, how do I add just the date portion of RequestDate to DecisionTime?
View 6 Replies View RelatedHi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())?
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
I need an SQL string that inserts the current date into a database.
So far I have tried:
SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')"
mycomm = New SqlCommand(sql, myconn)
mycomm.ExecuteNonQuery()
However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.
With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?
For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.
My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?
Thanks in advance for your help!
PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.
Hello I have and tested in 2 way using variable and constant.
Query 1: This query I am using vaiable "@last" for the date value.
this takes to run for 2 minute .
Query 2: Here I am using value '11/05/2002' instead of varialbe and it is running within a second.
Why these much difference to run this same query?.
Can anybody give correct reason or solution for this.
Thanks,
Ravi
query 1:
DECLARE @last smalldatetime
SET @last ='11/05/2002'
select distinct(o.account_num),a.enterprise_num,a.site_nu m
from oesd.dbo.order_header o WITH(NOLOCK)
INNER JOIN oesd.dbo.account a WITH(NOLOCK)
ON a.account_num = o.account_num
and o.added_date > @last
UNION
select distinct(o.account_num),a.enterprise_num,a.site_nu m
from oesd.dbo.order_header o WITH(NOLOCK)
INNER JOIN oesd.dbo.account a WITH(NOLOCK)
ON a.account_num = o.account_num
INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK)
ON (a.enterprise_num = es.ENTERPRISE_NUM
AND o.last_updated_date > @last)
Query 2:
select distinct(o.account_num),a.enterprise_num,a.site_nu m
from oesd.dbo.order_header o WITH(NOLOCK)
INNER JOIN oesd.dbo.account a WITH(NOLOCK)
ON a.account_num = o.account_num
and o.added_date > '11/05/2002'
UNION
select distinct(o.account_num),a.enterprise_num,a.site_nu m
from oesd.dbo.order_header o WITH(NOLOCK)
INNER JOIN oesd.dbo.account a WITH(NOLOCK)
ON a.account_num = o.account_num
INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK)
ON (a.enterprise_num = es.ENTERPRISE_NUM
AND o.last_updated_date > '11/05/2002' )
Hi,
We are migrating our database from DB2 8 to SQL Server 2005. We have date and time saperate columns in DB2. For example, Date_of_birth, Store_sun_open_time, Store_sun_close_time etc. For date we are using datetime. For time what datatype should we use in SQL Server?
Thanks
Prashant
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
I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.
To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.
The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below
CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]
By this above code i have created the expected datetime however
I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like
case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)
This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.
I have provided a screenshot of my data and a expected date column for reference.
Candidate StartDateStartTimeExpected DateTime Timepoint
1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR
[Code] ....
I have also attached the data for reference.
I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:
select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate,
CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTimeBut when I try to make it a single datetime field:
select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)
Which returns with: Conversion failed when converting date and/or time from character string.
So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...
hi i have question
can sql server know when the row in table Saved CREATE TRIGGER date time on the ROW ?
add new field call "date_row_save" date+time
inside the the sql server
i need to know whan the row Saved
is it possible to do this in TRIGGER ?
TNX
Hi There
We currently have the following scenario:
4 app servers with regional date and time settings of locale A.
1 database server with locale settings B.
What is happening is that timestamps are being generated on the app servers, these are then in a sql command which fails on the database server since the timestamp format is invalid.
It was suggested that we change the regional locale settings of the database server, but will this not have serious implications , for example every current timestamp format in the datbase will become invalid?
In a nutshell is it safe to change a database servers regional date time locale settings ? Or are there serious implications?
Thanx
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
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
Dear Expert!
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example:
When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007.
When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
Any advice would be greatly appreciated.
Kind Regards
Johan Rastenberger
I have a table that stores working hrs, such as
RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000
So working times can go over midnight, there can be more than one working period in a day etc.
For this staff member the summary of the weeks work will be
18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.
So for example
If this person
had a days holiday on the 22nd, shown in the HOLIDAY table as
StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000
A Leave of Absence on the 20th, shown in the LEAVE table as
StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000
And was off sick on the morning of the 19th, shown in the SICKNESS Table as
StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000
Now the Summary table should now show
18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?
I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.
We run std 2008 r2. I downloaded an rdl whose 2 date/partial time cols used to expand on the server to accommodate times with two digits in hr. When I preview in vs 2008, everything is ok. But when I copy back to the server, these longer date strings now wrap to next line. Why is this? Only change was to a param's drop down ds query. expression for one of the cols is
=Format(Fields!LoginDt.Value, "MM/dd/yy" &
" " &
"h:mm tt")
height is currently allowed to increase.