table with DateTime?

• Charmer

  SSChampion

  Points: 14903

  More actions

  March 30, 2012 at 8:13 am

  #255712

  Hi Friends,

  How to create a table with datetime?

  like tablename_getdate()

  Is that possible friends?

  Thanks,
  Charmer

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  March 30, 2012 at 8:17 am

  #1466959

  Yes. Try dynamic sql.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Charmer

  SSChampion

  Points: 14903

  More actions

  March 30, 2012 at 8:19 am

  #1466962

  PaulB-TheOneAndOnly (3/30/2012)

  ---

  Yes. Try dynamic sql.

  Gimme an example Please...

  Thanks,
  Charmer

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 30, 2012 at 8:21 am

  #1466966

  how about a view, so you have the date? is that what you want?

  Create view TableToday

  AS

  select

  DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) As TheDate,

  GETDATE() as TheDateTime,

  edit: misread the question...thought you wanted a table with the datetime, not a tablename:

  DECLARE @sql varchar(8000)

  SET @sql = 'CREATE TABLE [TABLE_'

  + DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

  +' ] (ID int,OtherColumns varchar(30) )'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  March 30, 2012 at 8:23 am

  #1466967

  Charmer (3/30/2012)

  ---

  PaulB-TheOneAndOnly (3/30/2012)

  ---

  Yes. Try dynamic sql.

  Gimme an example Please...

  Nothing beats vendor's documentation 🙂 check here: http://support.microsoft.com/kb/175850

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  March 30, 2012 at 8:27 am

  #1466969

  PaulB-TheOneAndOnly (3/30/2012)

  ---

  Charmer (3/30/2012)

  ---

  PaulB-TheOneAndOnly (3/30/2012)

  ---

  Yes. Try dynamic sql.

  Gimme an example Please...

  Nothing beats vendor's documentation 🙂 check here: http://support.microsoft.com/kb/175850

  also try here, http://bit.ly/HppLBX

• Charmer

  SSChampion

  Points: 14903

  More actions

  March 30, 2012 at 9:25 am

  #1467021

  Lowell (3/30/2012)

  ---

  how about a view, so you have the date? is that what you want?

  Create view TableToday

  AS

  select

  DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) As TheDate,

  GETDATE() as TheDateTime,

  edit: misread the question...thought you wanted a table with the datetime, not a tablename:

  DECLARE @sql varchar(8000)

  SET @sql = 'CREATE TABLE [TABLE_'

  + DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

  +' ] (ID int,OtherColumns varchar(30) )'

  I have already tried this one...but its concatenating not creating date & time...

  Thanks,
  Charmer

• venoym

  SSCarpal Tunnel

  Points: 4161

  More actions

  March 30, 2012 at 9:31 am

  #1467028

  maybe if you could write out a full sql definition example that you are looking for.

  Something like the below. Don't include the dynamic part, just what the output should look like.

  CREATE TABLE tblTemp

  (

  [ID] NUMERIC(18, 0),

  [Date_12312012] DATETIME,

  [Time_1200PM] DATETIME

  )

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 30, 2012 at 9:33 am

  #1467030

  Charmer (3/30/2012)

  ---

  Lowell (3/30/2012)

  ---

  how about a view, so you have the date? is that what you want?

  Create view TableToday

  AS

  select

  DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) As TheDate,

  GETDATE() as TheDateTime,

  edit: misread the question...thought you wanted a table with the datetime, not a tablename:

  DECLARE @sql varchar(8000)

  SET @sql = 'CREATE TABLE [TABLE_'

  + DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

  +' ] (ID int,OtherColumns varchar(30) )'

  I have already tried this one...but its concatenating not creating date & time...

  you did try executing the string you build after you concatenate? i just tested this, and it works fine, my first guess was concatenating date + varchar, so that was wrong:

  DECLARE @sql varchar(8000)

  SET @sql = 'CREATE TABLE [TABLE_'

  + convert(varchar,getdate(),112 )

  +'] (ID int,OtherColumns varchar(30) )'

  print @sql --does it look right?

  exec(@sql) --does it run right?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Charmer

  SSChampion

  Points: 14903

  More actions

  March 30, 2012 at 10:46 am

  #1467088

  Lowell (3/30/2012)

  ---

  Charmer (3/30/2012)

  ---

  Lowell (3/30/2012)

  ---

  how about a view, so you have the date? is that what you want?

  Create view TableToday

  AS

  select

  DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) As TheDate,

  GETDATE() as TheDateTime,

  edit: misread the question...thought you wanted a table with the datetime, not a tablename:

  DECLARE @sql varchar(8000)

  SET @sql = 'CREATE TABLE [TABLE_'

  + DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

  +' ] (ID int,OtherColumns varchar(30) )'

  I have already tried this one...but its concatenating not creating date & time...

  you did try executing the string you build after you concatenate? i just tested this, and it works fine, my first guess was concatenating date + varchar, so that was wrong:

  DECLARE @sql varchar(8000)

  SET @sql = 'CREATE TABLE [TABLE_'

  + convert(varchar,getdate(),112 )

  +'] (ID int,OtherColumns varchar(30) )'

  print @sql --does it look right?

  exec(@sql) --does it run right?

  Thank you Lowell...Its good..I get it what i expected...

  Thanks,
  Charmer