PHP/MS SQL - Automatic Formatting: Varchar, Datetime
Apr 10, 2008
Hi,
I want to change 2 default behaviors of ms-sql server within a connection.
1. All varchar (etc.) fields should be returned as UTF-8.
I know about field attribute collation and convert function,
but I want simple query like:
SELECT * FROM table;
to return all varchars as UTF-8, no matter how the fields were
defined and which collation.
In MySQL I execute: "SET NAMES 'utf-8';" after connecting to server
In PgSQL I use native function: pg_set_client_encoding()
2. Default datetime format is sth like this: "Apr 20 2008 12:01PM"
I just want all datetimes fields were returned as:
"2008-04-20 12:01:02"
I can do that with function convert() (style=20) but I want
(as in question above) a simple query like:
SELECT * FROM table;
to return date and time formatted as described above.
In MySQL and PgSQL the format "2008-04-20 12:01:02" is the default
one.
Is it possible to do that? If yes, please tell me how can I do that?
Wladyslaw
View 3 Replies
ADVERTISEMENT
Mar 12, 2008
I want to write some SQL which results in an automatic conversion of adatetime to a string in a format suitable for the Language of theconnection (either by explicitly setting the Language in theconnection string, or by setting the default language in for the userused for the connection.)The casting from string to datetime uses the language setting:Data Source=localhostsqlexpress;Initial Catalog=master;PersistSecurity Info=True; Language =BRITISH ENGLISH; Trusted_Connection=yes;Application Name = Test Application;select cast('13/01/2008' as datetime) --WORKS as expectedselect cast('01/13/2008' as datetime) --FAILS as expectedandData Source=localhostsqlexpress;Initial Catalog=master;PersistSecurity Info=True; Language =ENGLISH; Trusted_Connection=yes;Application Name = Test Application;select cast('13/01/2008' as datetime) --FAILS as expectedselect cast('01/13/2008' as datetime) --WORKS as expectedbut implicit casting the other way ignores the "Language setting" (iethe format is the same for both):BRITISH ENGLISHselect cast(cast('12/01/2008' as datetime) as nvarchar(max)) --Jan 122008 12:00AMselect convert(nvarchar(max),cast('12/01/2008' as datetime)) --Jan 122008 12:00AMENGLISHselect cast(cast('12/01/2008' as datetime) as nvarchar(max)) --Dec 12008 12:00AMselect convert(nvarchar(max),cast('12/01/2008' as datetime)) -- Dec 12008 12:00AMIs it possible to tell SQL Server "For language w convert datetimes tostrings using format x, but for language y use format z?"Regards,Dan
View 2 Replies
View Related
Aug 8, 2006
hi, good day, if i have a table as follow:
Create table test
(
student_name varchar(20),
student id varchar(20),
register_dt varchar(50)
)
and insert data like
insert into test values('rebecca','0001','2006-08-08 12:15:03')
after all , i would like to query the data and insert into another table say data_tbl where it define as follow
Create table data_tbl
(
student_name varchar(20),
student id varchar(20),
register_dt datetime
)
would the test table regiter_dt auto convert into datetime format or we need to convert it before insert into data_tbl table?
View 3 Replies
View Related
Jun 12, 2008
Hi guys,
How do you convert a date varchar field which looks like 20080525 to 2008/05/25?
View 3 Replies
View Related
Mar 19, 2008
Hello
I'm struggling with this one.......!!
I need to format a VARCHAR field storing a date value in to a different format.
The field currently holds date data in the format 01/01/2008 but I want to return 010108.
The nearest i'm coming out with is 01012008 by using the REPLACE function, but can't seem to get out the required format.
It is not a date field and I can't change it to a date field, due to other procedures relying on it.
Can anyone help??
Thank you
View 1 Replies
View Related
Jun 15, 2004
I need to automatically update a datetime field for a record to the current time whenever the record is updated.
create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go
One option would be to use an instead of update trigger.
create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go
update t set value='dick' where id=1 and name='fred'
go
Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.
Any ideas?
View 1 Replies
View Related
Jul 20, 2005
I'm trying to find a way to format a FLOAT variable into a varchar inSQL Server 2000 but using CAST/CONVERT I can only get scientificnotation i.e. 1e+006 instead of 1000000 which isn't really what Iwanted.Preferably the varchar would display the number to 2 decimal placesbut I'd settle for integers only as this conversion isn't businesscritical and is a nice to have for background information.Casting to MONEY or NUMERIC before converting to a varchar works finefor most cases but of course runs the risk of arithmetic overflow ifthe FLOAT value is too precise for MONEY/NUMERIC to handle. If anyoneknows of an easy way to test whether overflow will occur and thereforeto know not to convert it then that would be an option.I appreciate SQL Server isn't great at formatting and it would be fareasier in the client code but code this is being performed as adescription of a very simple calculation in a trigger, all stored tothe database on the server side so there's no opportunity for clientintervention.Example code:declare @testFloat floatselect @testFloat = 1000000.12select convert(varchar(100),@testFloat) -- gives 1e+006select cast(@testFloat as varchar(100)) -- gives 1e+006select convert(varchar(100),cast(@testFloat as money)) -- gives1000000.12select @testFloat = 12345678905345633453453624453453524.123select convert(varchar(100),cast(@testFloat as money)) -- givesarithmetic overflow errorselect convert(varchar(100),cast(@testFloat as numeric)) -- givesarithmetic overflow errorAny suggestions welcome...CheersDave
View 3 Replies
View Related
Aug 17, 2005
Is there a way of formatting a DateTime datatype, so e.g. "2005-08-17 18:27:16.753" could format to "dd/mm/yy hh:mm"?
View 2 Replies
View Related
Jun 13, 2002
I am selecting a datetime value from a table but I want it to be
in this format: mm/dd/yyyy
This query is as close as I have come:
select char(month(nancyDateSent))+ '/'+ datename(day,nancyDateSent) + '/' + datename(yy,nancyDateSent) from tbloutgoingobms_hold
but is not correct - I get dates that look like this: /11/2002
Please help - there must be an easier way !!!
Thanks in advance,
Nancy
View 1 Replies
View Related
Sep 11, 2007
hi,
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003
1/13/2007
01132004
1-1-2004
1.2.2001
there is no uniformity of the data that is stored currently.
thnkx in adv.
rahul jha
View 11 Replies
View Related
Oct 17, 2007
Hi,
I need to execute some store procedures I have in the SQL editor but I seem to been having problems with the formatting for datetime variables needed for the execution of my code. can anyone please help?
thanks in Advance
exec [usp_CMSTemplateCreateNewTemplate] 0, 0, 'My First Page', '', 0, CAST(10/16/2007,DATETIME), CAST(10/25/2007, DATETIME), 0, @PageTemplateID=0
THE STORED PROCEDURE SAMPLE
ALTER PROCEDURE [dbo].[usp_CMSTemplateCreateNewTemplate]
@SiteID AS INT,
@PageID AS INT,
@Title AS NVARCHAR(50),
@EditHREF AS NVARCHAR(512),
@CreatorID AS INT,
@StartDate AS DATETIME,
@EndDate AS DATETIME,
@Child AS INT,@PageTemplateID AS INT OUTPUT
AS
View 1 Replies
View Related
May 11, 2015
Part of my query is :
SELECT * FROM TableA
WHERE ColumnA >= DATEADD(DAY, - 30, GETDATE())
With the expression at the where clause above, you can pull a rolling 30 days data without having to supply values. Now users of the report want to see it represented like: 2nd April – 1st May
when the report is ran. the requirement is not to use a parameter for the reportKnowing that I have no parameters, how do I reference ">= DATEADD (DAY, - 30, GETDATE())" to reflect the start date and the end date in the report?
View 3 Replies
View Related
Apr 20, 2005
Hi:
I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime.
I use Query like:
SELECT
CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +CAST(SUBSTRING(date_sent,4,2) as int) + '/' +CAST(SUBSTRING(DATE_SENT,7,4) as int)
From MyTable
It is not working, anybody can give me some advise!
thanks!
View 4 Replies
View Related
Sep 17, 2007
Hi,
I wanted to convert the varchar to date time and here is what i am doing
DECLARE @dt VARCHAR(20)
SET @dt = '20070111' -- YYYYMMDD format
select CONVERT(datetime, @dt, 120)
This works perfectly fine and the result would be- 2007-01-11 00:00:00.000
But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing
"Conversion failed when converting datetime from character string."
Can any one please let me know how do we achieve this?
~Mohan
View 3 Replies
View Related
Oct 23, 2007
Hi,
I'm facing a small issue with date conversions. It would have been great if someone could help me out. I have a field in my database (SQL Server 2000) called SavDateTime of type varchar(50). I store dates in this field in the format "dd/MM/yy hh:mms". A sample date would be "23/10/2007 10:15:30 AM". Now I need to have an order by for this field, say like,
select * from sample order by SavDateTime desc
When I execute this query I get an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I tried using the convert method also like CONVERT(DATETIME,SavDateTime,103), but the problem still exists. It would have been helpful if you could provide me with a solution to this problem.
Thanks & Regards,
Frens
View 7 Replies
View Related
Apr 12, 2005
I have a textbox that is receiving a date in the format mm/dd/yyyy. The field in the database is VarChar size 10. I purposely don't want to use the DateTime data type for various reasons. When I extract the text from that textbox:
Example:4/11/2005
I do my INSERT. When I look in the database at that field, it shows up like:April 11, 2005 12
The database's field is VarChar! How/why is it doing automatic DateTime formatting?
View 3 Replies
View Related
Jan 21, 2012
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code:
Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID
Where D.SchoolID = @DojoID
and C.Days like'%' + @Days + '%'
Union
Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID
Where D.SchoolID = @DojoID and @Date
Between E.EventStartDate and E.EventEndDate
how can i cast it correctly?
View 1 Replies
View Related
Oct 2, 2014
I can't get a varchar to datetime conversion to work. The varchar data is in the form yyyymmddhhmi
I tried convert(datetime, '200508310926')
also tried cast('200508310926' as datetime) both have error "Conversion failed when converting date and/or time from character string"
Is there a format code that needs to be there? If so, I can't figure out what it should be.
I eventually need to convert these to dates and compare them to getdate, example:
...where convert(datetime, dtfield) >= getdate()-1
View 7 Replies
View Related
May 27, 2008
Hi All,
I have a table called Table1 with a field Date (varchar type). The records are like this;
05/21/08
07/02/08
06/04/08
06/10/08
I want to convert the above into datetime (yyyy-mm-dd format)....in the above example my output should be;
2008-05-21
2008-07-02
2008-06-04
2008-06-10
How can i do this?
Please help.
Zee...
View 2 Replies
View Related
Jun 17, 2008
Hello,
I have this column of time values:
00:00:03.3592675
00:00:00
00:00:03.8592515
00:00:03.6873820
00:00:03.8436270
00:00:03.3436430
It is in varchar format. I tried converting it to datetime but get an invalid format error message.
How can I sum up these values and average them out?
Thanks!
--PhB
View 1 Replies
View Related
Jun 19, 2014
I have a column on my table with the varchar(50) datatype. The whole column has the value 2014-04-31
I want to Convert the varchar(50)datatype to datetime datatype.
The table name is called - dbo.pracs
the column name is - LastDatUpaded
View 6 Replies
View Related
Dec 14, 2007
I have this procedure and it can't convert this date to datetime type:
Exec scnr.prc_tblScannersReemplazarClasificacion
'CERVEZA ',
'CERVEZA',
'ENV VAC ',
'ENVASE VACIO',
'NULL',
'NO aplica',
'NULL',
'NO aplica',
'1-8-2007',
'10-12-2007',
'ISCRacajina';
and after that i tried to execute it, but I changed the date like next:
Exec scnr.prc_tblScannersReemplazarClasificacion
'CERVEZA ',
'CERVEZA',
'ENV VAC ',
'ENVASE VACIO',
'NULL',
'NO aplica',
'NULL',
'NO aplica',
'8-1-2007',
'12-10-2007',
'ISCRacajina';
and the procedure reseive this parameter:
CREATE PROCEDURE scnr.prc_tblScannersReemplazarClasificacion(@tipoclasificacion varchar(200),
@categoriaV varchar(200),
@categoriaN varchar(200),
@subcategoriaV varchar(200),
@subcategoriaN varchar(200),
@tipoV varchar(200),
@tipoN varchar(200),
@subtipoV varchar(200),
@subtipoN varchar(200),
@fechascanner datetime,
@fechaingreso datetime,
@usuarioscanner varchar(200) )
The error is:
Msg 8114, Level 16, State 5, Procedure prc_tblScannersReemplazarClasificacion, Line 0
Error converting data type varchar to datetime.
Why can't this procedure reseive these dates?
What can I do?
Help me...
View 3 Replies
View Related
Mar 13, 2008
Hello,
I'm trying to CONVERT some DateTime column to VarChar in the SELECT part but still do a DateTime ORDER BY while using a UNION.
In other words, does anyone know how to make this work:
create table #temp1 (
d datetime
)
create table #temp2 (
d datetime
)
insert into #temp1(d)
select '2001-12-12' union
select '2002-11-11'
insert into #temp2(d)
select '2003-10-10' union
select '2004-09-09'
--works fine-------------------------------
select convert(varchar, d, 101) dd from #temp1
order by d
-------------------------------------------
--but can't make this work-------------------
select convert(varchar, d, 101) dd from #temp1
union select convert(varchar, d, 101) dd from #temp2
order by dd -- wrong type of sorting
--order by d-- error
--order by cast(dd as datetime) -- error
-------------------------------------------
drop table #temp1
drop table #temp2
I would really appreciate your help.
Thanks in advance,
Thomas
View 5 Replies
View Related
Feb 22, 2008
Hi,
I have a varchar field named FinancialYTDCode containing data in the format 2007F or 2008F. I want to only select the rows with the FinancialYTDCode that is the same as the current year.
Could someone please show me how I write this in my script.
Thanks
View 9 Replies
View Related
Aug 22, 2007
declare @a varchar(10)
select @a= shiftstarttime from o_parameter
print @a
declare @b varchar(10)
select @b= shiftendtime from o_parameter
print @b
declare @dt varchar(20)
set @dt=Left(getdate(),12)
print @dt
declare @dt1 varchar(20)
set @dt1=Left(dateadd(dd, 1,getdate()),12)
print @dt1
declare @dt3 varchar(20)
set @dt3= @dt1 + space(0) + @a
print @dt3
declare @dt4 varchar(20)
set @dt4= @dt + space(0) + @b
print @dt4
output of above script is as fallow
09.30am
06.30pm
Aug 22 2007
Aug 23 2007
Aug 23 2007 09.30am
Aug 22 2007 06.30pm
now i want to convert @dt3 and @dt4 into datetime .
because i wnat to calculate datedifference in hours by using this function
SET @in_hour=DATEDIFF (HH ,@D1,@D2)
where @D1 and @D2 are datetime parameters.
but how can i get @dt3 and @dt4 into datetime so i can pass it to DATEDIFF() function.
so plz Guide me. or if u know how to write it then plz write here
i already use cast for it but it doesn't work.
so plz reply urgently.
View 2 Replies
View Related
Jan 30, 2008
Basically, I'm trying to take two dates and find the difference in hours b/t the two, sql server 2000. I've tried change the variables out from datetime to varchars even though it shouldn't matter. When I put the block of code out of the stored procedure and run it in line it works fine, I'M LOST.........
I get the errors everytime it runs into the first block, I get the error again when the block is hit again farther down in the procedure. I get:
Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
ERR dates Aug 4 2005 11:16PM.
Server: Msg 242, Level 16, State 3, Procedure tmpRyan_spRail_Job_NoMovement, Line 211
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 242, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 360
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
ERR dates Aug 3 2005 12:35PM.
Server: Msg 241, Level 16, State 1, Procedure tmpRyan_spRail_Job_NoMovement, Line 211
Syntax error converting datetime from character string.
Code:
Declare
,@CodePair varchar(10
,@CodePairTimeDiff dec ,
@tmpTimeVar datetime ,
@tmpTimeCharVar varchar(30)
IF @NextStatusDate IS NOT NULL AND @StatusDate IS NOT NULL
BEGIN
set @tmpTimeCharVar = DATEDIFF(SECOND, @NextSTatusDate, @StatusDate)
set @tmpTimeVar =
(
CASE WHEN @tmpTimeCharVar/3600<10 THEN '0' ELSE '' END
+ RTRIM(DATEDIFF(SECOND,@NextSTatusDate, @StatusDate)/3600)
+ ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@tmpTimeCharVar % 3600) % 60),2)
)
END
IF @@ERROR <>0
PRINT 'ERR dates' + Convert(varchar(20), @NextStatusDate) + '.'
inside update statement
,CodePairTimeDiff = CONVERT(dec, (100 * DATEPART(HOUR, @tmpTimeVar)
+ DATEPART(MINUTE, @tmpTimeVar))) / 60
View 12 Replies
View Related
Mar 4, 2004
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
View 3 Replies
View Related
Oct 2, 2014
When you converting varchar to date time
SELECT convert(varchar,getdate())
What is the getdate here
i.e. we are converting any value to today's date
View 9 Replies
View Related
Mar 19, 2015
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies
View Related
Aug 14, 2015
I have column moddat which is of varchar(10,null)
Here is my data:
20020415
20020508
19991104
19990701
20040514
20021112
20020124
19990628
20020514
20010822
I want those data in this format YYYY-MM-DD
How to convert varchar to datetime?
View 2 Replies
View Related
Jun 14, 2015
I have a column in a sql Table with a datatype nvarchar 255 and the value in the column is 07MAR2012:00:00:00.000. I want to convert into a datetime.
View 4 Replies
View Related
Feb 16, 2007
Hi,
It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command.
I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters.
I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control.
When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio.
Is there anything about ADO that I do not know?
View 2 Replies
View Related
Jul 22, 2005
Can anyone help me on this!I've got more than a 1000 records in a SQL server database.The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it. thanks in advance!
View 1 Replies
View Related
