DateTime Dates Store Wrong

Dec 14, 2007

I have a stored procedure to add a poeersons DOB to a table. The date is being stored as Dec 12 1980

Instead of as 12/12/1980

The table uses a datetime data type.
The data type of the date being passed into the stored procedure is also set to datetime. (@DOB datetime) Even though the value passed into the procedure is 12/12/1980 It saves the date as Dec 12 1980

What do I need to do to fix this?





Miranda

View 2 Replies


ADVERTISEMENT

Integration Services :: Loading Datetime Field Give Wrong Dates In SSIS

May 30, 2015

I am using Sql Server 2012. I have a table which has a field as Datetime (it is a table in Dynamics CRM 2011 so I have no control of the data type). Say this field is called BisStartDate. If I run this query in management studio.

select
BisStartDate, BisStartDateutc
from myTable
where _bisnumber=10375

I will get:

BisStartDate                                             BisStartDateutc               

2014-07-29 00:00:00.000                      2014-07-29 05:00:00.000

*in CRM, datetime is saved in 2 fields, one is the current time, the other one is the utc time.

You can see the offset  between the datetime and utc is 5 hours.

However when the same statement was running inside a SSIS package on the server, the result returned is:

BisStartDate                                             BisStartDateutc               

2014-07-28 23:00:00.0000000            2014-07-29 05:00:00.000

And if I do

datediff(MINUTE,CRMAF_BisSection.ttc_section_startdatetimeutc,CRMAF_BisSection.ttc_section_startdatetime)

I will get -5 if I run it in ManagementStudio and -6 is running on server package(running inside VisualStudio will be -5, same as running a query in ManagementStudio).

I think when the record was saved, “date” is 5 hours offset to UTC time but now the system use the current utc offset which is 6 hours. I just want to use the BisStartDate as it is. How do I let the SSIS turn off the conversion.

The same datatime is saved in another system then we compare them to check the data entry. Now because of this one hour difference, sometime the Day will be different.

View 8 Replies View Related

Gather Format And Store - Right Or Wrong

May 4, 2006

The IT group that I work with has the habit of gathering data,formatting (i.e. in reports) and then storing the same formated data inthe same database.I think the practice is wrong. I think the activity is fundamentallywrong because we are storing the exact same data in a database in twodifferent locations. Somehow I have the impression that database designis about "oneness".I believe that collecting the data and then storing summerized data forreporting into a data warehouse would be the right solution.I am getting flack for my viewpoint.Am I all washed up?

View 1 Replies View Related

Wrong Datetime When Update Using MSSQL

Dec 11, 2005

Dim ldDatetime As DateTime = Now() '11/12/2005 13:05:09
my command text is:
"Insert Into WebSiteThread (TransactionDate) Values ( " & " ' " & ldDatetime & " ' " & ") "
The result I got was "12/11/3091 13:05:09" Not "11/12/2005 13:05:09"
What's wrong please!!
 

View 6 Replies View Related

Convert Datetime But Order By Is Wrong

Sep 25, 2006

I am trying to convert my datetime to 107 ex: Dec 11 2006 ,but when sorting I get the months sorted correctly but not by year????



ex:

Jan 2004

Feb 2001

Mar 2006

View 1 Replies View Related

I Can't Store Dates In The DD/MM/YYYY Format.

Oct 18, 2007

ok so this is my table structure
[odcCode] [int] NOT NULL ,
[insCode] [varchar] (11) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[odcQty] [int] NOT NULL ,
[odcCostoUnitario] [numeric](18, 4) NOT NULL ,
[odcISV] [bit] NULL ,
[odcStatusInsumo] [varchar] (3) COLLATE Modern_Spanish_CI_AS NULL ,
[odcInsArrivalDate] [datetime]

INSERT INTO OrdenCompraDetalle
(odcCode, insCode, odcQty, odcCostoUnitario, odcISV, odcStatusInsumo, odcInsArrivalDate)
VALUES
(697,'MIN-000028',1,22.3250,0,'PEN', '18/10/2007 12:00:00 a.m.')

and when I run, the insert command I get the next error:
Syntax error converting datetime from character string.


The date is taken from a standar DateTime Picker control from Visual Studio 2003 and I can't change the regional settings on the computers (that's not an option)

What can I do to fix the problem and be able to insert the date in any format?

View 3 Replies View Related

Problem Getting Recoreds From Store Proedure Between Dates...

Aug 23, 2007

i have a gridView and i want to get the recoreds between specific dates.. so i put two calendars to select the dates...
and they will filter the recoreds..(i seleced dates and it returns non recors and theres recored betwenn those dates and in a SQL View it works )
What can i do? Thanks
Store Proceure Function:
 CREATE PROCEDURE sp_FindTnoaGrid
@SearchTnoa nvarchar(14),
@beginDate as nvarchar(50),
@endDate as nvarchar(50)
AS
-- order by dbo.V_tnuot.t_erech desc--



if (@beginDate='%') or (@endDate='%')
SELECT dbo.V_tnuot.kod_lakoha, dbo.V_tnuot.t_erech, dbo.V_tnuot.t_peula, dbo.V_tnuot.scum_peula as sum,
dbo.V_tnuot.strHpoalimSugKodTnua, dbo.V_tnuot.DescSugPeula, cast(year(cast(@beginDate as datetime)) as nvarchar)+'-'+cast(month(cast(@beginDate as datetime)) as nvarchar)+'-'+cast(day(cast(@beginDate as datetime)) as nvarchar) as try,
dbo.V_tnuot.Hpoalim_HodeshSahar
FROM dbo.V_tnuot LEFT OUTER JOIN
dbo.tblCategTnuot_KodPeula ON dbo.V_tnuot.strHpoalimSugKodTnua = dbo.tblCategTnuot_KodPeula.idKodTnua

WHERE ( kod_lakoha = @SearchTnoa)

if (@beginDate<>'%') and (@endDate<>'%')
SELECT dbo.V_tnuot.kod_lakoha, dbo.V_tnuot.t_erech, dbo.V_tnuot.t_peula, dbo.V_tnuot.scum_peula as sum,
dbo.V_tnuot.strHpoalimSugKodTnua, dbo.V_tnuot.DescSugPeula,
dbo.V_tnuot.Hpoalim_HodeshSahar
FROM dbo.V_tnuot LEFT OUTER JOIN
dbo.tblCategTnuot_KodPeula ON dbo.V_tnuot.strHpoalimSugKodTnua = dbo.tblCategTnuot_KodPeula.idKodTnua

WHERE ( kod_lakoha = @SearchTnoa) and (dbo.V_tnuot.t_peula BETWEEN cast(@beginDate as datetime) and cast(@endDate as datetime) )
GO 

View 1 Replies View Related

Problem Comparing Dates In Store Procedure

Mar 17, 2004

Hi,

Below is my store procedure. I am trying to capture all orders between a startdate and a enddate. When I use the Where #1(see below- I have 2 where clauses) it allows me to go from one year to another. For example, startdate of 2/18/2003 - enddate of 2/19/2004 will return all orders in my table except for 2/19/2004. Probably because of the time portion.
When I use the Where #2, it doesn't go back a full year. The startdate is reacting like 2/18/2004 and not 2003. The records I get are only for dates 2/18/2004-2/19/2004.

How do I create a where clause which will go back a year or more and include the enddate orders? Any help will be very much appreciated.

Thanks,

poc1010


alter Procedure GetOrdersBetweenDates

@startdatesmalldatetime=null,
@enddatesmalldatetime=null

as

select o.orderid,(convert(varchar(12),o.orderdate,101)) as Date
,o.orderamt,o.shipamt,o.njtaxamt,o.totalamt
,c.bfirstname+' '+c.blastname as BName,c.baddress1,c.baddress2
,c.bcity,c.bstate,c.bzip
,c.sfirstname+' '+c.slastname as SName,c.saddress1,c.saddress2
,c.scity,c.sstate,c.szip
from orders o inner join customers c on o.orderid=c.orderid
1.)where o.orderdate between @startdate and @enddate
2.)where convert(varchar(12),o.orderdate,101) between
convert(varchar(12),@startdate,101) and convert(varchar(12),@enddate,101)
order by o.orderid

exec getordersbetweendates '2/18/2003','02/19/2004'

View 1 Replies View Related

DateTime Parameter Is In Wrong Format When Report Is Executed For The First Time

Dec 7, 2007

In my report I have two date parameters, both are of type DateTime.
The problem is that when the report is called for the first time the report parameter value is not shown in the expected format (de-DE, 01.01.2007) but in en-US 01/01/2007
Only when I change a Date using the calendar popup and click on the "show report" button, the right format will be taken over.

Language properties of the report are"=User!Language"
IE language is de-DE.
Report Server is a MS Windows Server 2003 R2 Standart Edition SP2
with MS SQL SERVER 2005 SP2


P.S.
on a server with MS SQL SERVER 2005 SP1, this report works fine.
may be this bug is a new feature of SP2?

View 13 Replies View Related

Select All Dates From SQL Datetime Field

Aug 3, 2007

I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?

View 3 Replies View Related

Problem To Store DateTime

Oct 30, 2007

Hi, I want store the DateTime value on DB in a Web Service. To do it i write this line:


execSQL("Insert INTO [UserDB].[dbo].[Accessi] ([UserID],,[Data]) VALUES( " + ID.ToString() + ",'" + IP + Host + " ',' " + DateTime.Now.ToString("dd/MM/yyyy HH:mms") + " ' );");

but I receive this exception:

The conversion of a char data type to a datetime data type resulted in a out-of-range datetime value.

I don't know because receive this exception.

Thank's

View 9 Replies View Related

SQL Query - DateTime - Percentage Between Two Dates For A Year (Jan. 01 - Dec. 31)

Jan 14, 2008

Hello, I need to find the percentage of a a given contract start and end date for the year given.For example, the contract_start date is 05/08/2000 and the contract_end date is 04/30/2010, of course this will be 100% if you want to find the percentage for year 2008    but if you wanted to find the percentage for year  2010, then the percentage would be something like 42% (appr. 5 months) (b/c it would for year 2010, the contract would be from 01/01/2010 thru 04/30/2010)I need to find the percent from the beginning of the year, to the end.a few examples:    start: 01/01/2007   end: 05/01/2007   if for year: 2007; this is 4 months @33.33%    start: 05/01/ 2006   end: 06/01/2007  if for year: 2007, then 6 months @ 50% (01/01/2007 - 06/01/2007); but if it was for year 2006 then it would be 7 months @ 58% (05/01/2006 thru 12/31/2006)     start: 01/01/2000   end: 03/01/2010 for year: 2008 then 12 months @ 100% Any help would be valued. Thank you! 

View 4 Replies View Related

Can Datetime Type Store Milliseconds

Aug 25, 2006

Hi,
I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...
Anyone knows how to store datetime value with milliseconds in the SQL database?
Thanks
 

View 13 Replies View Related

Store Time Only In A Datetime Datafield In MS SQL

Nov 3, 2004

Hi,

I want to store time into a datetime datafield in MS SQL Database, but eventually, it was include a date prefix the time ! I had try the approach on following and get the invalid result.

Approach 1:
Dim strTime as String = "11:59:59 AM"
Output:
01-01-1900 11:59:59 AM

Approach 2:
Dim dtTime as DateTime = "11:59:59 AM"
Output:
1/1/0001 11:59:59 AM <- (error occur: unable add to DB cause date is not between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM)

* The time on the above is accompany with AM/PM.

Approach 3:
Dim strTime as String = "11:59:59 AM"
Dim strStartTime2 = DateTime.ParseExact(strStartTime, "hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture)
Output:
01-01-1900 11:59:59 AM

Approach 4:
Dim strTime as DateTime = "11:59:59 AM"
Dim strStartTime2 = DateTime.ParseExact(strStartTime, "hh:mm:ss", System.Globalization.CultureInfo.CurrentCulture)
Output:
String was not recognized as a valid DateTime.

I know what cause the approach 4 get an error. That is because the strTime is declare as DateTime and that contain "AM/PM" in time.

My Question:
1]Can I just insert the time only to the DB without date?
2]If possible, what should I delcare in the variable(String/DateTime/..?)
3]How can I deal with the "AM/PM"? It must concate with time to identify daytime/night

If you know and have the solution, pls do me a favor and will be more appreciated.
Thank you

Calvin

View 2 Replies View Related

How To Store Actual Millisecond In Datetime?

Mar 20, 2007

Hello. I've seached the this forum and others for this question of mine but couldn't get any thing. Everyone know that in MSSQL, the precision of datetime is currently limited to 1/300th of a second. My question is, is there any way to store the actual millisecond in the a table? Also, later, able to pull it up from the table? What are the options?

Please advise.

Thanks,

Rick..

View 11 Replies View Related

Create A Datetime Dimension Table With Start And End Dates

Jul 12, 2006

Hi

I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like

01/01/2004 00:01:00

01/01/2004 00:02:00

01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00

I will use this time dimension in bussiness objects.

please provide me with the SQL sript to do this.

View 3 Replies View Related

Problems Converting Dates As Varchar To Datetime Data Type

Sep 11, 2007



Hi all,

I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.

I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.

Someone can help me? PLEASE

Thanks

View 8 Replies View Related

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

Transact SQL :: Only Store Datetime Values Down To Nearest Minute Automatically Without Using Trigger

Sep 25, 2015

Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?  

Here is a very basic example of what I am trying to do:

--example:  '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate() 

 --example: '2007-09-22 15:07:00.000'  this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)

View 24 Replies View Related

Sql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?

Feb 19, 2006

Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code:        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _        ByVal Email As String, ByVal Gender As Integer, _        ByVal FirstName As String, ByVal LastName As String, _        ByVal CellPhone As String, ByVal Street As String, _        ByVal StreetNumber As String, ByVal StreetAddon As String, _        ByVal Zipcode As String, ByVal City As String, _        ByVal Organization As String _        ) As Boolean            'returns true with success, false with failure            Dim MyConnection As SqlConnection = GetConnection()            Dim bResult As Boolean            Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection)            MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName))            MyCommand.Parameters.Add(New SqlParameter("@Password", Password))            MyCommand.Parameters.Add(New SqlParameter("@Email", Email))            MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender))            MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName))            MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName))            MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone))            MyCommand.Parameters.Add(New SqlParameter("@Street", Street))            MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber))            MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon))            MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode))            MyCommand.Parameters.Add(New SqlParameter("@City", City))            MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization))            Try                MyConnection.Open()                MyCommand.ExecuteNonQuery()                bResult = True            Catch ex As Exception                bResult = False            Finally                MyConnection.Close()            End Try            Return bResult        End FunctionThanks!

View 1 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date.  the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved