Inserting date time

• sqlrumble

  Default port

  Points: 1458

  More actions

  June 14, 2005 at 4:20 am

  #67710

  Hello,

  I need to insert a specific date into a datetime field.  The date is in a variable (lauthdate).  My TSQL syntax is as follows:

  UPDATE TM_RENEWALHD

  SET AUTH_DATE = convert(datetime,(convert(varchar(8), lauthdate,112)))

  lauthdate is has a date in the format of DD/MM/YY.  The data type in the database is datetime.

  when i run the above is sql query analyser I get the following error:

  Server: Msg 241, Level 16, State 1, Line 1

  Syntax error converting datetime from character string.

  I have used the above to get the current date replacing the lauthdate with GETDATE() and it works fine.

  I would apprieciate any assistance.

  Thanks

  SM

• Michael Du Bois

  SSCertifiable

  Points: 5163

  More actions

  June 14, 2005 at 4:32 am

  #565211

  Hi try changing the settings in the convert statement to 103 which is the British/French date format for input data. (dd/mm/yy  

  SET AUTH_DATE = convert(datetime,(convert(varchar(8), lauthdate,103)))

  HTH Mike

   

• sqlrumble

  Default port

  Points: 1458

  More actions

  June 14, 2005 at 4:42 am

  #565214

  Hello,

  thanks for your post

  tried this with no joy.

  SET AUTH_DATE = convert(datetime,(convert(varchar, 04/06/05,103)))

  

   

• Michael Du Bois

  SSCertifiable

  Points: 5163

  More actions

  June 14, 2005 at 4:54 am

  #565220

  edited to add a missing ) and an example

  If the date format of the input is dd/mm/yy use

  convert(datetime,@Var,3)

  DECLARE @Var varchar(20)

  DECLARE @DateVar datetime

  set @Var ='21/1/2005'

  set @DateVar =convert(datetime,@Var,103) --format dd/mm/yy string

  select @Datevar

  **********************************

  if the date format is dd/mm/yyyy use

  convert(datetime,@Var,103)

  DECLARE @Var varchar(20)

  DECLARE @DateVar datetime

  set @Var ='21/1/2005'

  set @DateVar =convert(datetime,@Var,103) --format dd/mm/yyyy string

  select @Datevar

  sorry I was not clear in my original post

  Mike

• Ed Phillips

  SSChasing Mays

  Points: 634

  More actions

  June 14, 2005 at 4:59 am

  #565221

  This should do the job, there were a couple of missing apostrophes and and no length had been declare for the varchar.

  SET AUTH_DATE = convert(datetime, (convert(varchar(8), '04/06/05', 103)))

  Hope it works.

  
  
  Ed Phillips

• Ed Phillips

  SSChasing Mays

  Points: 634

  More actions

  June 14, 2005 at 5:01 am

  #565222

  Sorry, missed above post. Also have a look at Books On Line with regards to the CONVERT function as this contains a good table for date conversion.

  
  
  Ed Phillips

• Michael Du Bois

  SSCertifiable

  Points: 5163

  More actions

  June 14, 2005 at 5:11 am

  #565224

  Thanks ED I missed the missing ' s and forgot to mention bol.

  Mike

• David A. Long

  SSCrazy Eights

  Points: 8421

  More actions

  June 15, 2005 at 1:44 am

  #565566

  SELECT CONVERT(varchar(8), lauthdate, 112)

  No need to convert to datetime T-SQL will do that for you.

  Using ISO date format works with DATEFORMAT = MDY, YMD, or DMY

  SET DATEFORMAT mdy

  GO

  DECLARE @datevar datetime

  SET @datevar = '20050614'

  SELECT @datevar

  GO

  SET DATEFORMAT ydm

  GO

  DECLARE @datevar datetime

  SET @datevar = '20050614'

  SELECT @datevar

  GO

  SET DATEFORMAT ymd

  GO

  DECLARE @datevar datetime

  SET @datevar = '20050614'

  SELECT @datevar

  GO

  Andy

• Leo Leong

  SSCommitted

  Points: 1615

  More actions

  June 15, 2005 at 2:31 am

  #565575

  Hi,

  normally i dont pass in date time value from client.

  in fact, i will pass in date time in string format.

  for example, if i want dd/mm/yyyy, i will pass in 20/06/2005.

  i encountered this issue before due to client's regional setting is different from server.

  so, if i pass in dd/mm/yyyy format, i can use CONVERT(DATETIME, @var, 103)

  however, some people is passing yyyymmdd to database.

  it is up to you.

  Leo

• sqlrumble

  Default port

  Points: 1458

  More actions

  June 15, 2005 at 5:03 am

  #565618

  Thanks for all the posts.

  I will give it ago.