DATE CONVERSIONS

Jul 25, 2007

HI ALL,

declere @d varchar(10)
declare @t varchar(10)

set @d=convert(varchar,'7/25/2007 10:10:53',101)
set @d=convert(varchar,'7/25/2007 10:10:53',108)

it returns as
@d=7/25/2007
@t=7/25/2007

but @t should be equal to 10:10:53

what's wrong in the conversions


Malathi Rao

View 2 Replies


ADVERTISEMENT

About Date Conversions

Feb 19, 2008

 Hi All,    I struck by a issue, In a table(tblMemberPayments) i had two columns one is to hold (month) as CHAR and other one is to hold (year)  as INT. Now i need to implement search for this table based on the dates given , I mean i need to get the records from this table in between any two dates given by the user (Eg : feb 2007 to feb 2008). While retriving i am unable to type cast the things as required for the above case. I am getting an error unable to convert varchar to datetime while using CONVERT FUNCTION the same thing a raises while using DATEDIFF FUNCTION  .help me if any one knows the solution . here are the couple of ways i tried    1) 'CONVERT(varchar(10),(tblMemberPayments.ExpirationMonth + tblMemberPayments.Expirationyear),1) between CONVERT(varchar(10),''feb 2007'',1)       AND CONVERT(varchar(10),''feb 2008'',1)'  Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value 'Apr' to data type int. 2)'datediff(m,Cast(tblMemberPayments.ExpirationMonth + tblMemberPayments.ExpirationMonthint as datetime), ''Feb 2008'') >= 0 and datediff(m, cast(tblMemberPayments.ExpirationMonth + tblMemberPayments.ExpirationMonthint as datetime), ''Feb 2007'') <= 0'   Thanks & Regards,   Sudheer.Y    

View 2 Replies View Related

Century Date Conversions

Jul 20, 2005

Hi,I'm trying to load date fields into SQLServer using DTS, but theformat of the raw data is the number of days since 1 Jan 1900. How doI convert this to a useful format, is there a standard conversionroutine?ThanksTim

View 2 Replies View Related

String To Date Conversions

Apr 6, 2006

Having some trouble converting strings to dates. I am using a .dtsx package and have figured out how to use a derived column to convert the string to a new string which is a valid date. The problem is now converting the column data type to date. I insert a new data conversion step to convert the vchar field to date, then when it runs it fails on this step. Error is

The conversion returned status value 2 and status text "The value could
not be converted because of a potential loss of data."


If I leave out the data conversion step and run the package, then manually change the data type to date it warns with the same message, but if I accept it converts correctly.

 

Here is my derived column code

REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")


Please Help

View 9 Replies View Related

Derived Column - Date Conversions?!?

May 1, 2007

It was my understanding earlier that -- it is mandatory to have a Derived Column for a DATE String in a Flat File
to be loaded properly into a table with "datetime" column type

However, i tried running my package with the Input Column as "DT_STR" and the Destination on the table is DATETIME.

Looks like the process went on fine.. Am i missing something here?

My Date Format from the source looks like -- "DD-MON-YY"

Regards

View 1 Replies View Related

Date Format Conversions In SSIS

Oct 11, 2006

Hi

I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.

I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.

any suggestions will be gratefully recieved.

Best regards

View 1 Replies View Related

Datetime Conversions

Dec 6, 2007

In my DB I have strings which are all 8 characters long and all pass the isDate == 1 thing so everything row can be converted into a date.

When I run a select convert(datetime, value) it gives me an error saying that it's unable to convert datetime from string. However when I insert everything into a temp table and run each row individually, it converts fine.

Any ideas as to what I'm doing wrong? I've checked the date settings on SQL 2005 SP2 and the host itself and everything seems correct. I've been stuck on this all day and any help is very much appreciated.

View 12 Replies View Related

Sum Column Values After Conversions

Apr 11, 2014

I have this query where I do certain conversions and on top of this how can I sum the column values.

'$ '+ Replace(CONVERT(varchar,CAST(Total_Amount AS money),1) ,'.00','') as Total_Amount,
'$ '+ Replace(CONVERT(varchar,CAST(Monthly_Amount As money),1),'.00','') as Monthly_Amount,

View 2 Replies View Related

Data Type Conversions

Sep 25, 2006

What are the pros/cons of datatype conversions using the advanced properties of the source adapters or transformation components themselves vs. using the data conversion transformation? Sometimes I am able to do all the conversions I need using the advanced properties rather than use a data conversion component, but didn't know if this was considered bad practice, or has some other impact that isn't immediately apparent.

Thanks

Kory

View 2 Replies View Related

Parameter Conversions Question From SQL Server

Mar 22, 2004

I have a question regarding a casting error I recieve when I explictly assign a value to a value in a stored procedure.......
The value pstrLoginName is a public string defined in a code module
The stored procedure is NVARCHAR Datatypes.....

Here is the Stored Procedure

CREATE procedure dbo.Appt_GetPermissions_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15),
@DeleteScan bit Output

)
as
select

@DeleteScan=UserP_DeleteScan
from
Clinic_Users
where
UserName = @LoginName
and
UserPassword = @Password

GO


Here is my error

Specified cast is not valid
Line 194: cmdsql.CommandType = CommandType.StoredProcedure
Line 195:
Line 196: parmLogin = cmdsql.Parameters.Add("@LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName
Line 197: parmPassword = cmdsql.Parameters.Add("@LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword
Line 198: parmDelete = cmdsql.Parameters.Add("@DeleteScan", SqlDbType.Bit)

my vb .net code to execute this

Public Sub ObtainPermission()


'get the needed data
Dim consql As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmLogin As SqlParameter
Dim parmPassword As SqlParameter
Dim parmDelete As SqlParameter

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure

parmLogin = cmdsql.Parameters.Add("@LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName <----error occurs here

parmPassword = cmdsql.Parameters.Add("@LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword <---I'm sure it will happen here then
parmDelete = cmdsql.Parameters.Add("@DeleteScan", SqlDbType.Bit)
parmDelete.Direction = ParameterDirection.Output

consql.Open()
cmdsql.ExecuteNonQuery()

'obtain rights

pstrPermissions = cmdsql.Parameters("@DeleteScan").Value
consql.Close()

End Sub

View 3 Replies View Related

SQL Data Type Conversions And DataRows

Sep 20, 2005

Hi all,I have a DataRow that I'm attempting to insert into SQL.  The row has an item I"ll refer to as row["AbsorbtionDeficiency"].  I'm filling this data row from a ODBC connection to a propriatary database.  Now I want to insert this data into SQL where the same row data type is "money".I'm not to familiar with the different data types, but the long and short of it is this.  When I build my SQL Insert statement, sometimes this value is blank, sometimes it's 0, and sometimes it will have a dollar amount(no formating  just the numeric).  How to I get this data out of the row in a format that SQL understands... Since I'm creating a  SQL string the datatype of the row I assume doesn't matter, so I"m doing a .ToString() when I build the statement.Do I need to do a CAST('0' AS MONEY) to each line that is money type?  Or is there an easier way?I would insert the code, but it's a pretty masive statement and I don't think it's really needed yet.ThanksJosh

View 1 Replies View Related

Sp_executesql Max 10 Data Type Conversions?

Apr 4, 2008

I can't get sp_executesql to accept more than 10 converted parameters in one execution. A stored procedure loops through a table variable that presents up to 100 parameters and their named data types. I've tried selecting into sql_variant and nvarchar variables and converting them to the named data type in the loop.
sp_executesql is outside the loop.
For the first 10 parameters sp_executesql recognizes the data type conversions of the variables that hold the parameter values, but then it fails to accept the conversions of the 11th and subsequent variables. Is there a limit to the number of conversions that sp_executesql can cope with?

View 5 Replies View Related

Import From Access - Datatype Conversions

Jul 6, 2006

I have to do a lot of inporting from Access files.  Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?

Thanks.

Kato

View 3 Replies View Related

How To Handle Multiple Column Conversions

Jan 26, 2006

I have multiple columns in a table that I only want to convert if they are not null or of the proper type. I don't really want to redirect the whole row if one of the conversions fails, rather I would just not want to do the cast or set it to another value. In this scenario is it better to use a custom script or multiple tasks? The multiple tasks seems like a lot of overhead for processing the same data. Just curious how others handle this scenario as it seems as though it has come up quite often on our current project? Thanks for the assistance.

-Krusty

PS: Is it possible to embed evaluation statements along with conversion statements in a task expression? e.g. (len(trim([Column1])) > 0) substring([Column1], 1, 4)

View 1 Replies View Related

Importing A Csv Into Sql Server, Problems With Field Conversions

Nov 13, 2004

I'm importing a csv file into sql server and everything is working fine however here are my problems.

The csv file fields contain some money fields and even though my db column type is money the dts package console complains that my data type from the csv is of type string 'for the money value' and cannot be inserted into the db field because they don't match. I changed the db field to nvarchar and hey presto it works fine. Problem is that this is no good because the data in the db is no longer a money value and therefore I cannot query the way I should.

So basically how do i map my csv to pass its values as the right types instead of just string values. If this was in c# i could just assign the parameter types accordingly but I'm not sure how to with sqlservers dts package.

Any help!!!!!

View 2 Replies View Related

DB Conversions - SQL Server 2000 X 2005 Express

Jun 1, 2006

Recently I decided to start using DotNetNuke, which uses an SQL Server database. My webhoster offers SQL Server 2000, and I was able to install and run DNN successfully there. However, I wish to have a local copy of DNN on my PC with the same contents as is on the web. It means that I need either to convert SQL Server 2000 database to my local SQL Server 2005 Express database, or vice versa.

Although I programmed in (Visual) FoxPro for many years, I am new to SQL Server and I am overwhelmed by the vast number of new terms. Can anyone tell me in simple English, how to perform the above mentioned conversions? My webhoster allows me to backup the SQL Server 2000 database in an *.sql file, but how shall I convert this file into an SQL Server 2005 Express *.mdf file?

View 6 Replies View Related

Username Password Conversions On Upgrade To 2005

May 23, 2008

Am getting ready to perform upgrade/migrate from 2000 to 2005 and have concerns about all the usernames/passwords converting accurately, as the existing 2000 instance has hundreds of them (am used to conversions with few usernames/passwords). Any suggestions as to the best way to proceed. The upgrade/migration is being done on the same box. Will a straight upgrade convert the master db cleanly with u/p, what issues does a migration on the same box present? Thanks.

View 4 Replies View Related

Alter Table Type Conversions With Default

Apr 29, 2008

I'm trying to write some code that will alter column to change its type, in this case from Nvarchar to Int, but I'm not sure if there will be any columns that are uncovertable (say, there's a string that does not have an Int value). In this case, I'd like it to default to 0. Is there a way to write a T-SQL statement to do this, or will I have to write an UPDATE stamement first to clear out any problem cases?

View 1 Replies View Related

How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date

Sep 21, 2006

HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance

View 3 Replies View Related

How To Convert A Date (date/time) To A Julian Date

Jun 13, 2002

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

View 2 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

SQL Server 2008 :: Finding Beginning Date From Multiple Lines Of Date Ranges?

Mar 20, 2015

I am trying to find a beginning date from multiple date ranges, for example:

RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03

For this id: 0793319, my beginning date is 2011-09-06

108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range

For this id: 0793319, my beginning date is 2012-09-04

108203492014-09-022015-06-30

For this id: 0820349, my beginning date is 2014-09-02

To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.

View 9 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

Mar 18, 2014

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]

View 7 Replies View Related

SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

Jul 20, 2005

Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated

View 5 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

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? 

View 24 Replies View Related

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

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

Flat File Text Date Conversion To SQL Server Date Comments And Suggestions

Mar 12, 2008

Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -

In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".

1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]

1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.


2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE

2.2) Select <add as new column> within Derived Column.

2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))

2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -

STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)

2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.

2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.

Please comment on the above, I will then pass on my suggestion to Microsoft.

Thanks in advance,

Kieran.

View 1 Replies View Related

How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?

Nov 13, 2007

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

View 1 Replies View Related

Super Urgent Codes To Compare Datafield Date With Today's Date

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

How To Select All Dates Upto Todays Date And Include The First Next Future Date

Jan 11, 2006

hello
how can i select all dates upto todays date and include the first next future date in a given data base

say todays date was the 01/06/2006 (MM,DD,YYYY)

below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006

i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006

so the results would return

1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006

View 2 Replies View Related

Unable To Insert Converted Date Into Date Column (Data Type)

Aug 24, 2015

PHP Code:

INSERT INTO [GPO].dbo.tblMetric  (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) 
SELECT 
    
      1 AS KPI_OWNER_ID
    , 23 AS METRIC_ID 
    , .75 AS GOAL 
    , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH
    , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS

[Code] ....

The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.

View 3 Replies View Related







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